Improving LIMIT X,Y query

[-]

I have only 1m records in my database running on a laptop of speed
1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk.

I use 'LIMIT x,10' for the query to utilise record paging.
When the value of x is nearer to 0, the query speed is fast.
Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes
about 0sec, 11secs and 4mins respectively.

1) Is there anything I should do, in terms of sql statement or database
design, to obtain the same speed for cases where x is in the middle or
nearer to the end?

2) How fast is a simple select statement (not select count(*)) on a
system like mine?

3) Suppose I left my original select .. limit query as is, what is the
minimum hardware that I need to improve the speed?

A million thanks.

[Jeff North]

On Thu, 19 Oct 2006 17:55:25 +0800, in mailing.databas e.mysql -
<no****@home.co m>
<45********@new s.starhub.net.s gwrote:

>| I have only 1m records in my database running on a laptop of speed
| 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk.
|
| I use 'LIMIT x,10' for the query to utilise record paging.
| When the value of x is nearer to 0, the query speed is fast.
| Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes
| about 0sec, 11secs and 4mins respectively.
|
| 1) Is there anything I should do, in terms of sql statement or database
| design, to obtain the same speed for cases where x is in the middle or
| nearer to the end?
|
| 2) How fast is a simple select statement (not select count(*)) on a
| system like mine?
|
| 3) Suppose I left my original select .. limit query as is, what is the
| minimum hardware that I need to improve the speed?
|
| A million thanks.

Use Explain [your query goes here]
then look at the results. Which table(s) are accessed most frequently
during the queries execution.
Can some of the fields be indexed.
Are the joins (if any) optimised.
How much data are you bringing through (specifying 20 fields but only
displaying 2).
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[-]

Use Explain [your query goes here]
then look at the results. Which table(s) are accessed most frequently
during the queries execution.
Can some of the fields be indexed.
Are the joins (if any) optimised.
How much data are you bringing through (specifying 20 fields but only
displaying 2).
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

My query is a very simple one.

SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;

Where X,Y are constants.

There are only two fields, a primary key and an indexed field.

[Jeff North]

On Thu, 19 Oct 2006 23:19:35 +0800, in mailing.databas e.mysql -
<no****@home.co m>
<45********@new s.starhub.net.s gwrote:

>|
| Use Explain [your query goes here]
| then look at the results. Which table(s) are accessed most frequently
| during the queries execution.
| Can some of the fields be indexed.
| Are the joins (if any) optimised.
| How much data are you bringing through (specifying 20 fields but only
| displaying 2).
| ---------------------------------------------------------------
| jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
| ---------------------------------------------------------------
|
|
| My query is a very simple one.
|
| SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
|
| Where X,Y are constants.
|
| There are only two fields, a primary key and an indexed field.

Well you've got me.
A couple of things though.
You don't say what OS you're using. Shutdown your mySQL server and
defrag your system. Maybe the files are all over the place and mySQL
has to do extra work to retrieve the data.
Run your query again - any noticable difference

What table type are you using myISAM or INNODB?
You could try create a copy of your table (minus the PK and index)
then import the data from the orginal table to the duplicate. Then add
your PK and index. I'm thinking that the indices maybe in need of a
freshen up. Try your query on the duplicate table, is there any speed
difference? Also my adding the PK and index fields later this will
give you an indication of the time required for mySQL to rebuild the
data - handy for when you drop pk and drop index/create pk/index.

What else is running on your system? Is there a lot of disk thrashing
when you are doing the slow query. Maybe the system is running out of
resources and needs to do a lot of memory swapping.
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[-]

Jeff North wrote:

>
Well you've got me.
A couple of things though.
You don't say what OS you're using. Shutdown your mySQL server and
defrag your system. Maybe the files are all over the place and mySQL
has to do extra work to retrieve the data.
Run your query again - any noticable difference

What table type are you using myISAM or INNODB?
You could try create a copy of your table (minus the PK and index)
then import the data from the orginal table to the duplicate. Then add
your PK and index. I'm thinking that the indices maybe in need of a
freshen up. Try your query on the duplicate table, is there any speed
difference? Also my adding the PK and index fields later this will
give you an indication of the time required for mySQL to rebuild the
data - handy for when you drop pk and drop index/create pk/index.

What else is running on your system? Is there a lot of disk thrashing
when you are doing the slow query. Maybe the system is running out of
resources and needs to do a lot of memory swapping.

Suppose everything is in order, how long will such query on a two-field
table of only 1m records takes on windows xp, 1.6Ghz, 512mb?

SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
where X and Y are constants.

Thank you, Jeff.

[Jeff North]

On Sat, 21 Oct 2006 09:01:38 +0800, in mailing.databas e.mysql -
<no****@home.co m>
<45******@news. starhub.net.sgw rote:

>| Jeff North wrote:
| >
| Well you've got me.
| A couple of things though.
| You don't say what OS you're using. Shutdown your mySQL server and
| defrag your system. Maybe the files are all over the place and mySQL
| has to do extra work to retrieve the data.
| Run your query again - any noticable difference
| >
| What table type are you using myISAM or INNODB?
| You could try create a copy of your table (minus the PK and index)
| then import the data from the orginal table to the duplicate. Then add
| your PK and index. I'm thinking that the indices maybe in need of a
| freshen up. Try your query on the duplicate table, is there any speed
| difference? Also my adding the PK and index fields later this will
| give you an indication of the time required for mySQL to rebuild the
| data - handy for when you drop pk and drop index/create pk/index.
| >
| What else is running on your system? Is there a lot of disk thrashing
| when you are doing the slow query. Maybe the system is running out of
| resources and needs to do a lot of memory swapping.
|
| Suppose everything is in order, how long will such query on a two-field
| table of only 1m records takes on windows xp, 1.6Ghz, 512mb?
|
| SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
| where X and Y are constants.
|
| Thank you, Jeff.

Shouldn't take upto 4 minutes.
Have your tried:
EXPLAIN SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
or
DESCRIBE SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
they might be able to show you the bottleneck.
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------