473,233 Members | 3,071 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,233 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 4724
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: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
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: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.