Improve a big mysql select performance

Question

I have a select to get products from sellers that an custormer follows. this products has likes, comments, need to count likes, need to count comments and show another things. I have a where user in to get custumers that user follows.

The problem is, this select is taking a while and I'd like to know if I can improve it somehow. All my ids are keys.

select c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, count(DISTINCT likes.user) as likes_count, count(distinct comentarios.id) as comentarios_count, count(DISTINCT l2.user) as count2 

from products p 

join users c on p.user=c.id 
left join profile_picture pp on p.user = pp.user
left join likes on likes.post = p.id
left join comentarios on comentarios.foto = p.id and comentarios.delete = 0  
left join likes l2 on l2.post = p.id and l2.user = ?

where (p.user in (select following from following where user =? and block=0) or p.user=?) and p.delete='0'
group by p.id
order by p.id desc limit ?

explain:

Answer 1

Apply Index in products table with delete and user column (use composite index).

Apply appropriate index ind users table

SELECT c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, COUNT(DISTINCT likes.user) AS likes_count, COUNT(DISTINCT comentarios.id) AS comentarios_count, COUNT(DISTINCT l2.user) AS count2 

FROM products p 

LEFT JOIN users c ON p.user=c.id 
LEFT JOIN profile_picture pp ON p.user = pp.user
LEFT JOIN likes ON likes.post = p.id
LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0  
LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = ?

WHERE c.id IS NOT NULL AND  (p.user IN (SELECT following FROM following WHERE USER =? AND block=0) OR p.user=?) AND p.delete='0'
GROUP BY p.id
ORDER BY p.id DESC LIMIT ?

Use above query may give you better performance

Answer 2

TL;DR

From your explain, it looks you don't have an index key products on (user, delete, id). this is likely the biggest bang for the current query.

I'm going to assume since you're asking this you don't know about EXPLAIN and even if you did, probably would have difficulty parsing out direction to move on what to do if you did read it.

In the short, assuming you've no major storage/memory issues to deal with and can add indexes to your tables, I would suggest you need to have the following indexes on your tables:

• Composite index for table products on (user, delete, id)
• Composite Index for table likes on (post, user)
• Composite index for table comentarios on (foto, delete) . I HATE COLUMNS NAMED AFTER RESERVED WORDS (like DELETE)!!!!
• Composite index for table following on (user, block)

This will make the joins efficient but is not 100% covering, which is likely not desirable in this case from your select values.

There may already be existing indexes that fit the above, any that start wit the values above (in the exact order) in the table it's noted for will be good enough. For example, if you've an index for table likes on (post, user, some_other_column) it will already provide everything needed by my suggestion of index on (post, user). The key is the values needed in from it has to be ordered exactly the same.

Now there's a ton of nuance to indexing and a lot you'll want to learn, but this should unblock your work.

Just tossing out a few additional things:

• You should name foreign keys after the table and column they relate to. For example comentarios.foto would be better named comentarios.products_id if indeed it's the id from the products table. Such clear naming will help prevent bugs/errors, improve efficiency writing new queries, and minimize loss of sanity for future developers trying to grok your schema.
• As mentioned by several commenters, learn about how to read a Mysql EXPLAIN on you queries. Just literally add the keyword EXPLAIN before your query and run it. The result lays out what the query engine initially plans to do to execute your query (may actually deviate during execution, but typically pretty spot on) . From this you can see where an index may be needed to improve execution.