473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.
Oct 19 '06 #1
5 4731
On Thu, 19 Oct 2006 17:55:25 +0800, in mailing.database.mysql -
<no****@home.com>
<45********@news.starhub.net.sgwrote:
>| 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******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Oct 19 '06 #2
-
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******@yourpantsyahoo.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.
Oct 19 '06 #3
On Thu, 19 Oct 2006 23:19:35 +0800, in mailing.database.mysql -
<no****@home.com>
<45********@news.starhub.net.sgwrote:
>|
| 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******@yourpantsyahoo.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******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Oct 19 '06 #4
-
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.
Oct 21 '06 #5
On Sat, 21 Oct 2006 09:01:38 +0800, in mailing.database.mysql -
<no****@home.com>
<45******@news.starhub.net.sgwrote:
>| 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******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Oct 21 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Steve | last post by:
I've run in to a problem with a query I'm trying to write. I have attached a sample SQL script at the end of this post to show an overview of what I'm working with. I want to be able to use...
2
by: Mike | last post by:
New to PHP and MySQL. Using PHP5 and MySQL 4.1 Windows XP Pro IIS 5.1 I'm trying to page a recordset, and am using a LIMIT clause to fetch a defined range of records from my db. However, the...
4
by: emily_g107 | last post by:
Hi, I need to limit results in the following query type: ...
0
by: D. Dante Lorenso | last post by:
I need to know that original number of rows that WOULD have been returned by a SELECT statement if the LIMIT / OFFSET where not present in the statement. Is there a way to get this data from PG ?...
2
by: elein | last post by:
Yes, I vacuumed. Reproduced on both 7.3.2 and 7.5. Brain dead java beans want order by clauses in views that they use. my view is: select .... from bigtable b left join lookuptable l order...
1
by: Larry Neylon | last post by:
Hi, I'm working on a VBScript application on IIS6 and I'm looking for some advice about the best way of replacing or improving session variable usage. The application is in a secure extranet...
1
by: lawrence k | last post by:
Want to replace the limit clause in a query, but can't get it right. What's wrong with this: $pattern = "(.*)limit (.*)"; $replacement = '$1'; $replacement .= "LIMIT $limit"; $replacement .=...
3
by: supsupmo | last post by:
Let's say I have a table, data_tbl, with the following columns: object_id of type integer mytimestamp of type timestamp with time zone data_1 of type integer data_2 of type integer And each...
11
by: burtonfigg | last post by:
I've read this article: http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/ullman-ajax.html And from reading it thought it'd be interesting to try a bit of Ajax on my site. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.