FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Caution: LIMIT is much much slower than WHERE





imagefree
I am curious about the mysql performance for the past few days, and to test mysql at its extreme i decided to create an empty table with just one id (autoincrement, primary key, mediumint, indexed) field filled and rest of 13 fields empty. I Inserted 16700000+entries to table (near about the maximum number size of mediumint). Now my table size has increased to 318.5 MB and index is 147.3 MB.


After this i executed a query in PHPMYADMIN:

Code:
SELECT *
FROM `links`
LIMIT 15865140 , 30


and it took about 6+ seconds. (A few queries are taking more than 12 seconds.) This caused me to think again about the performance and reliability of mysql for heavy web application, but today morning i have found an interesting thing that made me run my similar query in just .0006 second.

The changed query was:

Code:
SELECT *
FROM `links`
WHERE li_id >3699800
AND li_id <3699831


(change the values and) both queries are doing the same thing.
The difference between the queries is that the first query with limit is searching the actual table for the record # 15865140 and next 30 records, while the second query is searching index for searching records between two specified values.

Previously i used Limit very often in my queries but now what i decide to do is first apply some king of 'where' on the indexed field(s) and then use LIMIT to select from the results because most of the time WHERE clause can do the work of LIMIT of atleast it can support LIMIT. Using only LIMIT is much much expensive (as shown above).

what do u say? (i am no a mysql expert. its just my observation.)
rvec
kinda obvious. With the first query you load the whole table and pick 30 records out of that, with the second you directly pick those 30. Try the difference if the id isn't indexed or a primairy key, should be much smaller because the where query becomes a lot slower.
AftershockVibe
These statements are only equivalent if your "id" field is incremental and, more importantly, none of the records have been deleted.
imagefree
AftershockVibe wrote:
These statements are only equivalent if your "id" field is incremental and, more importantly, none of the records have been deleted.

yes ofcourse
Related topics
`Things to know for real Computer NOOB'
Is Jesus Perfect?
All About WoW
C/C++
Driving and Speeding
Is Astrology a Science?
Where to fish
Against Overclocking OR For Overclocking?
Time Travel: Is it Possible?
Pagination
Bird Flu
phpmyadmin problem
God exists - and here's the proof
homepage needs to be updated
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.