MySQL count optimization

Question

I want to decrease the load time from 2.5 secs for a mysql count query. All columns in the query have a index.

SELECT COUNT(1)
FROM song AS s
JOIN song_text AS st
  ON(st.song_id = s.song_id)
JOIN phpfox_user AS u
  ON(u.user_id = s.user_id)
WHERE st.lyrics LIKE '%a%' AND s.is_active = 1 AND s.public = 1

The query getting the returned rows load in 0.0009060 seconds.

SELECT s.*, st.lyrics, u.first_name
FROM song AS s
JOIN song_text AS st
  ON(st.song_id = s.song_id)
JOIN phpfox_user AS u
  ON(u.user_id = s.user_id)
WHERE st.lyrics LIKE '%a%' AND s.is_active = 1 AND s.public = 1
ORDER BY s.date_added DESC 
LIMIT 12

Why does the count query have a significantly more load time than the query returning the rows? What can be done to reduce the load time for the count query to something similar to other query?

Have you tried running `EXPLAIN` on the queries? This should give you insights. I believe @AdityaNaidu may be correct - the `LIMIT` in the second query may be filtering a lot of the work needed in the first query. — evan, Feb 07 '12 at 22:28
Remove the `LIMIT` clause from second query and post the query time. Use `SELECT SQL_NO_CACHE ` to remove cache from the equation. — Salman A, Feb 07 '12 at 22:40
Removing the limit from the second query caused the query time to be with 7.6967690 seconds. — WeekendCoder, Feb 07 '12 at 22:53
Removing the order by and the limit changed the load time to be 5.0759008 seconds. The question I would like to know is how to reduce the count query time to something less than 0.5 seconds. — WeekendCoder, Feb 07 '12 at 22:57

score 0 · Answer 1 · answered Feb 07 '12 at 22:15

0

Do you care about the u.first_name value in the count? If no, remove the 2nd join, it appears to add no value.

Also, try count(s.song_id) from the song table instead of count(1). I'm not sure if this is a real optimization or just my imagination.

answered Feb 07 '12 at 22:15

DwB

37,124
11
56
82

score 0 · Answer 2 · answered Feb 07 '12 at 22:22

0

Your second query completes once it has go the first 12 records. Whereas your first (count) query has to join all the columns in all tables.

This is the only difference I can see!

answered Feb 07 '12 at 22:22

Aditya Naidu

1,362
9
12

score 0 · Answer 3 · answered Feb 07 '12 at 22:26

0

I think it is because of

LIMIT 12

You only catch only 12 lines of your select statement.

answered Feb 07 '12 at 22:26

frugi

605
7
26

score 0 · Answer 4 · answered Feb 07 '12 at 22:33

First one has to look at execution plans of the queries.

I suppose that the temporary tables in the 1st query are quite big, therefore the aggregation is applied on a large amount of data.

In the other side LIMIT 12 already tells the query processor that it does not make sense to elaborate all of the rows but only 12, so that the temporary tables are small.

MySQL count optimization

4 Answers4