| ORDER BY … LIMIT Performance Optimization [message #741] |
Sat, 17 February 2007 11:58  |
 howachen
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
refer to the article:
ORDER BY … LIMIT Performance Optimization
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limi t-performance-optimization/
I am facing this problem. I am using the correct index, but the problem is on "large LIMIT", e.g.
ORDER BY ... LIMIT 10000,10
we can't avoid this problem in our application, but are they workaround?
can data partitioning help?
thanks.
|
|
|
| Re: ORDER BY … LIMIT Performance Optimization [message #743 is a reply to message #741 ] |
Sat, 17 February 2007 13:50  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
If you have LIMIT 10000,X means MySQL will have to generate and throw away first 10000 rows which is not overly efficient and partitioning is not directly helpful here.
I'd ask the question describing task you're trying to solve rather than judging it way you can do things.... there are usually many ways.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: ORDER BY … LIMIT Performance Optimization [message #811 is a reply to message #741 ] |
Mon, 26 February 2007 10:24 |
donny
Messages: 1
Registered: February 2007 |
Junior Member |
|
|
I'm facing a similar problem to the original poster's, perhaps you can help.
The problim is: I have items that belong to a certain category. You can select which categories to view, and browse through these items by page.
For example, you might view page 1000 of categories 1, 2, 10, and 30.
Sample table:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`category` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `cat` (`category`)
) ENGINE=MyISAM
Sample query to view page 1000 of cateogies 1,2,10,30:
SELECT id FROM test WHERE category IN(1,2,10,30) ORDER BY id DESC LIMIT 10000,10
I have the ORDER BY id DESC because the latest items are added at the end, so page 1 would be the last rows of the table.
I don't know how to optimize this query - as you said, this kind of query is generating 10,010 rows and throwing away all but 10 of them.
There will ultimately be millions of items, but not hundreds of millions (but there will potentially be 100,000 added daily). There will be less than 200 categories, and the user will need to be able to select any categories they want and browse through the pages.
Any ideas?
|
|
|
|
| Re: ORDER BY … LIMIT Performance Optimization [message #814 is a reply to message #813 ] |
Mon, 26 February 2007 18:19 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
I'd look if this causes performance problems for you.
Alexey is a bit wrong about index scan - LIMIT will actually perform row read and then throw data away, unless it is fully index covered query.
My first suggestion is to limit how far you can go - you can't go to page number 1000 even in Google. Restricting number of pages you really show works for many cases.
If you absolutely need to show all pages and you only update data once per day - you can pre-generate positions within each of category.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
