query optimization of a left join I have the following query written into the slow log (mysql-slow.log).

I don't have much idea how to optimize this query. I already have indexes for:


mode,
f.user_id... etc 


but doesn't seem to be helping me. Mind you, the table Engine is InnoDb.

# Query_time: 10.830652  
    Lock_time: 0.000058  
    Rows_sent: 1  
    Rows_examined: 18194  
    Rows_affected: 0
# Bytes_sent: 75
SET timestamp=1439531119;
SELECT 
  COUNT(u.user_id) 
FROM (
    users AS u 
    LEFT JOIN photos AS f 
        ON u.user_id=f.user_id 
        AND f.main='Y')  
WHERE  u.mode NOT IN('suspended','deleted','hidden')  
AND u.activated='Y';
Often times slow queries are caused by a lack of index on a column that is being used in a where or join clause.

Try indexing all of the following:


u.user_id
f.user_id
f.main
u.mode
u.activated


You could also try moving f.main='Y' to the WHERE condition - I believe it will result in the same outcome.