MySQL ORDER BY LIMIT Performance Optimization

Hi! Great article (as always). Just wanted to point out one quick exception to this:

“Do not sort by expressions”

On a FULLTEXT index, if you have a MATCH … AGAINST … in the WHERE expression as well as the ORDER BY expression (for instance, for the purpose of ordering by relevancy), then the FULLTEXT index will indeed by used and the ORDER BY will be quite efficient, as it is *not* reexecuted.

Cheers, and keep up the fantastic articles, Peter and Vadim!

Jay

Something got stripped away:

Is there a best practice to tackle this issue with PHP5/MySQL5, especially when I only need little amounts (less than 20) of random rows?

One way is to have something like a unique rowid in that table. Then in the script choose a random value and try to select a row matching that number.

One algorithm would then be:

1) SELECT MAX(rowid) AS maxrowid FROM t1 LIMIT 1; (we know that lowest rowid is 0 since its unsigned int).

2) Run the random() function in your script which will return a numeric value between 0 and MAX(rowid) (dont forget to seed).

3) SELECT col FROM t1 WHERE rowid >= “numeric value from 2)” LIMIT 1;

4) If 0 rows were returned check to the other direction: SELECT col FROM t1 WHERE rowid = “numeric value from 2)” LIMIT 1;

Sorry, sql should read:

SELECT * FROM WHERE MATCH(text_field) AGAINST(‘text’) ORDER BY score DESC

Hi.

At first I have to say this is the excellent site! 🙂

And now my troubles. I discovered range and order by field in index is the last one used.
Suppose I have a table with a, b and c fields and SQL query

SELECT * FROM table WHERE a=1 AND b>10 ORDER BY c

key(a,b) works, but filesort is used.
key(a,c) works, but b range goes sequentially.
key(a,b,c) doesn’t work (only a and b is used).

Do you have any idea how to go around this? How to define index for a, b and c?

Thank you anyway,
Dan

Hi

Your articles posted here are great. I’m a beginner in mysql optimization and i have a “maybe stupid” question.
Let’s say i have a table mytable(a,b,c,d,e …) and i want to perform ORDER BY with LIMIT on more than 60-70% of the columns (i want to have many sorts available)
example queries:
select a,b,c FROM mytable ORDER BY a;
select a,b,c FROM mytable ORDER BY b;
… etc…

is it ok to define indexes for every column used in order by? When the indexes defined on a table are considered to be too many (assuming that are not redundant are are used for different purposes…)

You say “Sort by column in leading table”, but could it be an workaround to create a view by joining 2 tables and then sort by any colum you want (from the view). Or is the same thins as SELECT with join and ORDER BY

use subquery with in FROM, like

select * from (select * from tab where … limit 0, 20) order by rand() limit 0,1

Peter,

It is semi- or fake random to minimum sort and table scan. I have 5 robot client run in the same time. It is for make sure the 5 client don’t pick up the same record out of 10,000 records.

It is random order in first 20, instead of random order in all (10,000 rows in my case). Performance is much better.

for 5 clients, 5 is OK instead of 20 in the sql. even put 100 there the performance won’t be big difference. I put 20 there, in case I increase the robot number.

Good article. I didn’t realize the join order would affect the ORDER BY clause.

Hey guys, here’s another good solution. I have a users table where I want to select five people at random who have a certain setting on their profile. I can’t use max ID because the IDs won’t necessarily be in sequence, so here’s what I do:

Select all the user IDs that match my params and put them into an array…

$query = “SELECT user_id FROM users WHERE profile_privacy=’0′ AND profile_display_name””;

… Then use PHP to shuffle the resulting array and take the first five items:

shuffle($random_profile_ids);
$random_profile_ids = array_slice($random_profile_ids,0,5);

… Then do an IN query on the user_id field for the five items left in your array:

$in_sql = “‘” . implode($random_profile_ids,”‘,'”) . “‘”;
$query = “SELECT * FROM users WHERE user_id IN ($in_sql) LIMIT 5”;

On my 30k row table the new solution ran in a combined .0147 secs, compared to .0770 secs for ORDER BY RAND(). Not a bad savings if you ask me. Enjoy,

Rob

I have a question regarding order by and limit..

i used to use this system in order to page the results 100 rows at a time but found it to be very slow as the page numbers grew larger and larger..

what i thought to help it scale better is to have something like this:

SELECT inverted_timestamp FROM whatever WHERE visible = ‘yes’ ORDER BY torrents.inverted_timestamp LIMIT 100,1

the inverted_timestamp field and the orderby go together as i m sorting by a few different columns (its like a spreadsheet table which u can page and sort by a few fields – i have added indexes for all the used cases).. the 100 is just the currentpage*perpage which happens to be 100 since i m on the 2nd page (page 1) and have 100 rows per page.

then i do the main query to fetch the actual results:

SELECT users.username,bla bla….. FROM whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users ON torrents.owner = users.id WHERE visible = ‘yes’ AND inverted_timestamp>=’3111748733′ ORDER BY inverted_timestamp LIMIT 100

which i have indxed as (visible,inverted_timestamp) (and similarly for the rest of the columns i order by)
the value >=’311..’ comes from the 1st query which i use to essentially find the first row of that page and then use that value in the main query to prevent the main one (which fetches all the results) to have to do an order by in the entire data set… From my testing it seems to be running very well upto around 1-2 million rows.. from then on the second query is still very fast but the first one is starting to take the hit.. previously i had just teh second query with no >= condition and i used limit offset,perpage which was ok up until 100k rows and then it was really slow…

it seems this solution is an improvement .. my question really is, is there a way to make it better?

to illustrate what i meant above these are the queries generated with a different ordering:
SELECT size FROM whatever WHERE visible = ‘yes’ ORDER BY size DESC LIMIT 3800,1
SELECT users.username,bla bla FROM whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users ON owner = users.id WHERE visible = ‘yes’ AND size

hmm it doesnt like my posts.. it cut it again :/

size

Here is my logic to do random.

$entries = array();
$count = 10;
while ($count > 0) {
$DB->query(“SELECT col1,col2 FROM entries AS e1 INNER JOIN members ON userid=id AND active=1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(entryid) FROM entries)) AS entryid) AS e2 WHERE e1.entryid >= e2.entryid AND e1.photos!=’a:0:{}’ ORDER BY e1.entryid ASC LIMIT 1”);
if ($DB->get_num_rows() > 0) {
$count–;
$entries[‘entries’][] = $this->__processEntry($DB->fetch_array());
}
}

It works pretty well for my standards

That was extremely helpful. I was having a hard time locating good and clear resources on limit and order optimization. Thanks!

Once again a post has been cut into pieces.

But it has one problem. What if you intend to look for multiple rows with another criteria.
Lets say you have 100.000 rows and want to output 5 randomized rows. From the 100.000 rows 200 qualify to be in the resulting rows. You can not run the query 5 times because you might end up with the same row 5 times. If there is a large gap between two qualifying results it can happen that you generate a random number that is in the gap thus giving you the same row over and over again.
Secondly you may hit the end of the set thus generating no output at all. Some ppl suggested to run the query backwards and forwards. i.id (smaller-equal) a.id and the i.id (greater) a.id
This brings trouble as well because the random number is generated inside the query resulting with two different random numbers. This can be overcome by making a pre-query to generate a random number in your application, still leaving you with the first problem.

SELECT *, MATCH(title, url, stripped) AGAINST(’test’ IN BOOLEAN MODE) as score FROM sites
WHERE stat = ‘A’
ORDER BY score
having score >0

How to optimized this query in PHP

$sqls=”select id,state from states”;
$rss=mysql_query($sqls) or die(mysql_error());
$num_rowss=mysql_num_rows($rss);

$count=1;

while($num_rowss && $datas=mysql_fetch_assoc($rss))
{

$state_id=$datas[‘id’];
$state=$datas[‘state’];
$sqlc=”select * from companies,package where comp_status=1 and comp_account_site=0 and comp_balance >0 and comp_mem_pac=pac_id order by pac_amount DESC, rand()”;

$rsc=mysql_query($sqlc) or die(mysql_error());
$num_rowsc=mysql_num_rows($rsc);

$checkonlyonetime=1; // header
while($num_rowsc && $datac=mysql_fetch_assoc($rsc)) // loop of company
{

$comp_id=$datac[‘comp_id’];

$comp_name=$datac[‘comp_name’];
$comp_mapped_city=$datac[‘comp_mapped_city’];
@$comp_mapped_city=explode(“,”,trim($comp_mapped_city,”,”));

}
}

Here SELECT MAX(post_id) FROM (SELECT …) is not necessary. It’s redundant
(it’s obvious ‘cos that subquery extracts only one post_id – see LIMIT x, 1)

SELECT *
FROM forum_posts AS pa
LEFT JOIN forum_posts_text AS pb ON pa.post_id = pb.post_id
LEFT JOIN forum_users ON user_id = post_poster
WHERE post_topic_id = ‘224’
AND pa.post_id >= (
SELECT post_id
FROM forum_posts
WHERE post_topic_id = ‘224’
ORDER BY post_id ASC
LIMIT 6075 , 1)
ORDER BY pa.post_id ASC
LIMIT 15

One tweak that we do for large limit offsets on pagination is to first select columns that are part of the index. Typically this would be the id’s. After that we use an “IN” to select the the other data columns from the same table. We find that there is a vast improvement on selects that use large offsets.

Example (category,id index)
select photo.id, photo.description from photo where category=1 limit 30000000,20
Query time: 4 seconds

To speed things up we split the call into 2 queries

select photo.id from photo where category=1 limit 30000000,20
Query time: 0.030 (this just uses the index so no traversing the data table)
Select photo.id, photo.description from photo where photo.id IN (x1,x2…x20)
The x1 – x20 are the results the first query returns
Query time: 0.020

So with a simple change like this we sped things up by a couple of orders of magnitude and allow our users to use large pagination.

I assume it has something to do that the the first query only uses the index to get the ids and does not have to jump around getting a larger data set.

Cheers.

Yes Sergio, You dont need to do the count as usually you have the page number or offset in the get varaible as you go from page to page. Typically we do a count first to create all the pagination stuff. Current page, total pages, etc.

Example of it in action here. http://www.pinkbike.com/photo/list/?date=all&page=6
You can page to page 100,000 (24 per page, so the offset is 2.4 million) without and slow down. If this was done in one call it literally takes seconds to generate offsets of 2,000,000

Yes, you can order by in the first query by id no problem. The id is indexed so no penalty there. Also your result set can be ordered by also but that is fast as you only have 24 items, or however many you show on the page.

Cheers

Hi, was wondering if there is a way to stop a SELECT COUNT(*) from continuing once it reached a max number of rows. Eg, i want to do something like:

SELECT count(*) FROM table_a LIMIT 1000;

So if the table has more than 1k rows, it should just return and say “1000”. What i’m trying to avoid is have the entire table scanned, in particular if it has millions of rows. Basically the question i want to ask is: “Does table X have at least N rows?”.

bozek, is it possible that your key_buffer size is set too small and therefore the whole index for your 30 million records is not large enough and therefore not caching the index? Not on my application this is set larger than the total index sizes for ALL tables and not just this one.

Is there anyway to accomplish the same thing as this query, avoiding a temporary table?

select ID_MEMBER_STARTED, COUNT(*) as hits from smf_topics group by ID_MEMBER_STARTED order by hits desc limit 20;

There is an index on ID_MEMBER_STARTED and the table type is InnoDB.

My query performance is very slow for large database.Table A has 2050702 records in it. ID is primary key and index on time.
SELECT ID, Name, Time from TableA WHERE Time >= ‘2009-03-24 12:53:00’ and ID > 2050702
Order By ID DESC LIMIT 100

I would like to know ways to optimize the query above. SO retrieve last 100 records from TAble A matching the criteria in where clause

Sorry, that should be “Add an index on” not “Add a query on”.

Good article. I didn’t realize the join order would affect the ORDER BY clause.

I have a query :

In this table category_id, date_created is indexed.

EXPLAIN select newsid from news where category_id=53 and approval=’yes’ order by date_created desc limit 0,10

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news index PRIMARY,category_id date_created 9 NULL 216 Using where

the above query examines 216 rows to fetch only 10 records.

Whereas the below query examines 10 rows which it should, (here i am not searching by category_id):

EXPLAIN select newsid from news where approval=’yes’ order by date_created desc limit 0,10

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news index NULL date_created 9 NULL 10 Using where

Why does the first query examine 216 rows? Is this fine or does the query need to be optimised futher.

Please advice, thanks in advance.

Hello Peter,

Thanks for the quick response.

I have removed the index on category_id. After removing it Explain for the below select query is as follows:

EXPLAIN select newsid from news where category_id=53 and approval=’yes’ order by date_created desc limit 0,10

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news index NULL date_created 9 NULL 10 Using where

This is fine i guess.
Another question is: If there are 56 records for category_id=53 and i want to display 100 records for all given categories in news table, why does below query examine 100 records for category_id=53 when there are only 56 of them?

EXPLAIN select newsid from news where category_id=53 and approval=’yes’ order by date_created desc limit 0,100

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news index NULL date_created 9 NULL 100 Using where

Most of the rows are displayed based on category_id, so I want to know if I am doing it correctly.

If this is confusing I could send the table structures and queries.

In this post you have mentioned about DOS attack, do you mean paging records can cause DOS attack ?

Any help would be highly appreciated.

Hey Peter,

I’ve been reading your articles for some time now, especially since I’ve been running into issues with wrapping my head around this whole MySQL optimization stuff.

Anyways, I get the jist of it, however, I run a real estate website (displays listings, etc, in a search results page), and there are just too many variables to try and cover an index on each scenario… I’d end up with 20 indexes, and I fear that any update/insert/delete queries to that table would blow up the internet.

I’m not looking for anybody to write me anything, I’m just looking to get a better understanding of this:

SELECT
id,
… more *necessary* columns …
FROM
listings
WHERE
category = 1
AND country = 1
AND state = 45
AND city = 35859
AND visible = 1
ORDER BY
price ASC
LIMIT 0, 10

Now, category numbers represent different listing types, ie. 1 = For Sale, 2 = For Rent, 3 = Pending Sale, and so on. 1 (For Sale is by the far the most popular, representing at least 75% of all listings in the table; probably more).

country, state, city are always in the query. visible is either a 1 or 0. 1 meaning the listing has not expired, and 0 meaning it has. Probably 85%+ instances of visible = 1 in the table.

Now, there are also other WHERE conditions that are possible, like WHERE price = 300000, or bedrooms = 2, or bathrooms = 3, etc. And who knows what the user is going to enter in.

Then, there is the order by on top of that.

What I have been looking for is how to accommodate these kinds of variables when there are so many possible scenarios? When I do an ORDER BY price DESC, it can take 4-5 seconds, maybe more… I guess that’s not the end of the world, but our traffic is always growing, and that will end up being unacceptable.

Any thoughts?

– Batman

Thanks for this artikel!! Was what I was looking for.

Hello Peter,

I am going through your article its found pretty much inserting. I need one help as i am displaying my property query which is displaying properly and ordering the result by following 4 column

p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,””),”2015-12-31″,””) DESC,
p.listing_order DESC,p.propid DESC

In order site, properties are been displayed in this order: Moveup paid, Paid featured, paid, trial then expired. However, we want to show within each group the properties random and make them rotate. Now,
the properties do not rotate so it is not fair for users to see always the same properties in same order. So, we need the rotation to be within same group not mixing all.

Query :-

SELECT DISTINCT(p.propid),p.category_id,p.people_sleep,p.bedrooms,p.bathrooms,p.children,
p.airport,p.features,p.pets,p.smoke, p.wheelchair,p.prop_title,p.property_type,prop_date,
display_calender,other_url_calender,is_feature
FROM property p
LEFT JOIN category ON (p.category_id=category.id)
WHERE p.active IN(“yes”,”d”) AND p.is_feature >= SYSDATE()
ORDER BY p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,””),”2015-12-31″,””) DESC,
p.listing_order DESC,p.propid DESC

Can you please help me into this.

Thank you so much for this great post! It was so much of a huge help and just exactly what I needed to know and understand right now. It is always great how you can present a topic so logically and make it look so easy and understandable even for not yet highly experienced people like me! 🙂

Peter, in the 2nd example where the query was “SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10”, you suggested that using an index on (date_created,category_id) might fasten the look-up. But if you use this index, queries won’t be able to use the index on category_id because it comes 2nd in the compound index. So won’t this compensate the gain earned by using the compound index?

Thanks.

Hello everybody,

We tried the solution base on query see in older post :
SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10

changed to

select * FROM (SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC) as my_table LIMIT 10

It works for us !

We’ve 1 query written in our legacy system. (using MySQL 5.5)
Now, with growing data – the below mentioned query taking huge time.
In our system, we’ve somewhat 200,00,00,000 (2 billion rows) approx 650 GB of data.
Table is partitioned with respect to every day. (which means above query is fetching data from 30 partitions).

=> Allocate 16GB to innodb_buffer_pool_size.
=> Index is there on START_TIME

Query-1
SELECT * FROM ( SELECT a,b,c,d,e,f,g,h,i,j,k,l FROM TEST WHERE START_TIME between ‘2013-11-14 00:00:01’ and ‘2013-12-14 23:59:59’ ORDER BY START_TIME DESC) as TEST_DATA LIMIT 10000;

Above Query => Means selecting all the columns for all the data between 1 month and performing sorting and at last show 10000 records to end user.

Now, my doubt goes: Query-2
SELECT a,b,c,d,e,f,g,h,i,j,k,l FROM TEST WHERE START_TIME between ‘2013-11-14 00:00:01’ and ‘2013-12-14 23:59:59’ ORDER BY START_TIME DESC limit 10000;

Above Query => selecting all the columns from 1 month of data and perform sorting and display the result as soon as 10000 records sorted. (No sorting and buffering of all records).

With Query-1 and Query-2 ->
1> Does these 2 queries will display different result set? Or same?
2> Any other impact on performance?

Because in Query-1, we doing sorting on all records and then display 10k
whereas in Query-2, we doing display 10k sorted records.

Thanks a lot for your help.

waka waka

Thanks for this good informations!