How i can optimize my mysql query?

Question

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

Answer 1

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.

Answer 2

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".

Answer 3

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

Answer 4

Use joins instead of a comma-delimited FROM cluase:

SELECT a.*, u.avatar, u.name, u.surname, u.username, COUNT(a.user1) AS cnt  
FROM following f
LEFT JOIN user_actions a ON a.user1 = f.follower_id
LEFT JOIN users u ON u.user_id = a.user1
WHERE f.user_id = 159 
GROUP BY a.user1, a.action, day(a.dt) 
ORDER BY a.id DESC 
LIMIT 7;

I chose to use the following table in the FROM clause as the key part of your search is based on following.user_id. This should reduce the number of rows fetched as you're narrowing down the result set as much as possible early on.

I recommend a LEFT JOIN as this will return rows from following even if they don't match any entries in the other two tables, which is usually the result you'd want in this kind of query. If you only want rows that have data in all 3 tables, try INNER JOIN instead.