How i can optimize my mysql query?
SELECT a.*, u.avatar, u.name, u.surname, u.username, COUNT(a.user1) AS cnt
FROM user_actions a,users u,following f
WHERE a.user1=u.user_id AND
a.user1=f.follower_id AND
f.user_id=159
GROUP BY a.user1, a.action, day(a.dt)
ORDER BY a.id DESC
LIMIT 7;
Query took 4.4909 sec
Indexes for table user_actions:
Action Keyname Type Unique Packed Column Cardinality Collation
Edit PRIMARY BTREE Yes No id 516094 A
Edit user1 BTREE No No user1 15639 A
Edit user2 BTREE No No user2 36863 A
Edit action BTREE No No action 16 A
Edit dt BTREE No No dt 516094 A
Edit group_index BTREE No No user1 20643 A
EXPLAIN SELECT a . * , u.avatar, u.name, u.surname, u.username, COUNT( a.user1 ) AS cnt
FROM user_actions a, users u, following f
WHERE a.user1 = u.user_id
AND a.user1 = f.follower_id
AND f.user_id =159
GROUP BY a.user1, a.action, day( a.dt )
ORDER BY a.id DESC
LIMIT 7
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE f ref user_id,follower_id,for_actions for_actions 4 const 242 Using index; Using temporary; Using filesort 1 SIMPLE a ref user1,group_index group_index 4 pictify_main.f.follower_id 25 1 SIMPLE u eq_ref PRIMARY PRIMARY 4 pictify_main.a.user1 1 Using where
Can't understand ur indexes because of formatting
However following indexes can help..
user_actions(user1)
following (user_id)
users(user_id)
else as suggested, please post explain plan.
Non-deterministic GROUP BY: The SQL retrieves columns that are neither in an aggregate function nor the GROUP BY expression, so these values will be non-deterministic in the result.
SELECT *: Selecting all columns with the * wildcard will cause the query's meaning and behavior to change if the table's schema changes, and might cause the query to retrieve too much data.
Aliasing without the AS keyword Explicitly using the AS keyword in column or table aliases, such as "tbl AS alias," is more readable than implicit aliases such as "tbl alias".
I would reverse it some since you are looking for a specific "following" user ID... then add clause of "STRAIGHT_JOIN" to tell MySQL to do in order explicitly listed
Ensure indexes for Table Index Following (User_ID, Follower_ID) Users (User_ID) User_Actions (User1,Action,Dt,ID)
select STRAIGHT_JOIN
UA.*,
U.Avatar,
U.Name,
U.SurName,
U.UserName,
count(*) as UserActionsCount
from
Following F
JOIN User_Actions UA
on F.Follower_ID = UA.User1
Join Users U
on UA.User1 = U.User_ID
where
F.User_ID = 159
group by
F.Follower_ID,
UA.Action,
Day( UA.Dt )
order by
UA.ID DESC
limit
7
上一篇: MySql查询超慢
下一篇: 我如何优化我的MySQL查询？