CODEWITHSUNDEEP
mysql
Invictus26
Invictus26

Reputation: 32

mysql slow query when results are less than limit

i've a table with 550.000 records

SELECT * FROM logs WHERE user = 'user1' ORDER BY date DESC LIMIT 0, 25

this query takes 0.0171 sec. without LIMIT, there are 3537 results

SELECT * FROM logs WHERE user = 'user2' ORDER BY date DESC LIMIT 0, 25

this query takes 3.0868 sec. without LIMIT, there are 13 results

table keys are:

PRIMARY KEY  (`id`),
KEY `date` (`date`)

when using "LIMIT 0,25" if there are less records than 25, the query slows down. How can I solve this problem?

Upvotes: 0

Views: 595

Answers (1)

Solarflare
Solarflare

Reputation: 11106

Using limit 25 allows the query to stop when it found 25 rows.

If you have 3537 matching rows out of 550.000, it will, on average, assuming equal distribution, have found 25 rows after examining 550.000/3537*25 rows = 3887 rows in a list that is ordered by date (the index on date) or a list that is not ordered at all.

If you have 13 matching rows out of 550.000, limit 25 will have to examine all 550.000 rows (that are 141 times as many rows), so we expect 0.0171 sec * 141 = 2.4s. There are obviously other factors that determine runtime too, but the order of magnitude fits.

There is an additional effect. Unfortunately the index by date does not contain the value for user, so MySQL has to look up that value in the original table, by jumping back and forth in that table (because the data itself is ordered by the primary key). This is slower than reading the unordered table directly.

So actually, not using an index at all could be faster than using an index, if you have a lot of rows to read. You can force MySQL to not use it by using e.g. FROM logs IGNORE INDEX (date), but this will have the effect that it now has to read the whole table in absolutely every case: the last row could be the newest and thus has to be in the resultset, because you ordered by date. So it might slow down your first query - reading the full 550.000 rows fast can be slower than reading 3887 rows slowly by jumping back and forth. (MySQL doesn't know this either beforehand, so it took a choice - for your second query obviously the wrong one).

So how to get faster results?

Have an index that is ordered by user. Then the query for 'user2' can stop after 13 rows, because it knows there are no more rows. And this will now be faster than the query for 'user1', that has to look through 3537 rows and then order them afterwards by date.

The best index for your query would therefore be user, date, because it then knows when to stop looking for further rows AND the list is already ordered the way you want it (and beat your 0.0171s in all cases).

Indexes require some resources too (e.g. hdd space and time to update the index when you update your table), so adding the perfect index for every single query might be counterproductive sometimes for the system as a whole.

Upvotes: 1

Related Questions