Tips to improve this slow mysql query?

Question

I use a query that usually runs in a second, but sometimes takes 10 to 40 seconds to complete. I actually don’t quite understand how the subquery works, I just know that it works because it gives me 15 lines for each faverprofileid file.

I register slow queries and tell me that 5823244 rows were checked, which is odd, because in any of the tables there is nowhere near many rows (the favorites table has the largest number of 50,000 rows).

Can someone suggest me some pointers? Is this a subquery problem and need to use filesort?

EDIT: Following the explanation shows that the users table does not use an index (even if the identifier is the primary key). The section "Advanced" says: the use of temporary; Using filesort.

SELECT F.id,F.created,U.username,U.fullname,U.id,I.* FROM favorites AS F INNER JOIN users AS U ON F.faver_profile_id = U.id INNER JOIN items AS I ON F.notice_id = I.id WHERE faver_profile_id IN (360,379,95,315,278,1) AND F.removed = 0 AND I.removed = 0 AND F.collection_id is null AND I.nudity = 0 AND (SELECT COUNT(*) FROM favorites WHERE faver_profile_id = F.faver_profile_id AND created > F.created AND removed = 0 AND collection_id is null) < 15 ORDER BY F.faver_profile_id, F.created DESC; 

Answer 1

I think with GROUP BY and HAVING it should be faster. Is this what you want?

 SELECT F.id,F.created,U.username,U.fullname,U.id, I.field1, I.field2, count(*) as CNT FROM favorites AS F INNER JOIN users AS U ON F.faver_profile_id = U.id INNER JOIN items AS I ON F.notice_id = I.id WHERE faver_profile_id IN (360,379,95,315,278,1) AND F.removed = 0 AND I.removed = 0 AND F.collection_id is null AND I.nudity = 0 GROUP BY F.id,F.created,U.username,U.fullname,U.id,I.field1, I.field2 HAVING CNT < 15 ORDER BY F.faver_profile_id, F.created DESC; 

I don’t know which fields from items you need, so I put placeholders.

Answer 2

The number of lines examined is large because many lines have been examined more than once. You get this because of a poorly optimized query plan that results in table scans when performing index searches. In this case, the number of rows examined is exponential, that is, an order comparable to the product of the total number of rows in more than one table.

• Make sure you run ANALYZE TABLE on the three tables.
• Read how to avoid table scans and identify, then create all missing indexes
• Repeat ANALYZE and explain your requests again.
  • the number of lines checked should decrease sharply
  • if not, publish a full explanation plan
• use query hints to force the use of indexes (to see index names for a table, use SHOW INDEX ):

SELECT F.id,F.created,U.username,U.fullname,U.id,I.*
FROM favorites AS F FORCE INDEX (faver_profile_id_key)
INNER JOIN users AS U FORCE INDEX FOR JOIN (PRIMARY) ON F.faver_profile_id = U.id
INNER JOIN items AS I FORCE INDEX FOR JOIN (PRIMARY) ON F.notice_id = I.id
WHERE faver_profile_id IN (360,379,95,315,278,1)
AND F.removed = 0
AND I.removed = 0
AND F.collection_id is null
AND I.nudity = 0
AND (SELECT COUNT(*) FROM favorites FORCE INDEX (faver_profile_id_key) WHERE faver_profile_id = F.faver_profile_id
AND created > F.created AND removed = 0 AND collection_id is null) < 15
ORDER BY F.faver_profile_id, F.created DESC;

You can also change your request to use GROUP BY faver_profile_id / HAVING count > 15 instead of the nested SELECT COUNT(*) subquery, as suggested by vartec . The performance of both the original and vartec requests should be comparable if both of them are correctly optimized, for example. using hints (your query will use nested index queries, while the vartec query will use a hash strategy.)

Answer 3

I suggest you use the Mysql Explain Query to see how your mysql server handles the query. My bet is that your indexes are not optimal, but the explanation should be much better than my bet.

Answer 4

You can loop for each id and use the limit instead of the subquery count (*):

 foreach $id in [123,456,789]: SELECT F.id, F.created, U.username, U.fullname, U.id, I.* FROM favorites AS F INNER JOIN users AS U ON F.faver_profile_id = U.id INNER JOIN items AS I ON F.notice_id = I.id WHERE F.faver_profile_id = {$id} AND I.removed = 0 AND I.nudity = 0 AND F.removed = 0 AND F.collection_id is null ORDER BY F.faver_profile_id, F.created DESC LIMIT 15; 

Answer 5

I assume that the result of this query is intended to be displayed as an uploadable list. In this case, perhaps you could consider a simpler “unrelated query” and make a second query for each row to read only 15, 20, or 30 elements. Wasn't there a heavy operation? This would simplify the query, and it would not become slower while increasing the joined tables.

Please tell me if I am wrong.