473,323 Members | 1,589 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,323 software developers and data experts.

Adding Order by slows my query!

I have the following query that runs quickly (0.5 secs):

select ZipListMatrix.ZipListMatrixID, ZipListMatrix.ZipID,
ZipListMatrix.CarrierRouteID,ZipListMatrix.ListID,
ZipListMatrix.Quantity,List.DisplayOrder
from ZipListMatrix
join List
join UserList on List.ListID = UserList.ListID
on ZipListMatrix.ListID = List.ListID
where UserList.UserInfoID = 869
and ZipListMatrix.ZipID = 23112
order by ZipListMatrix.ZipID

But as soon as I change the order by to "order by ZipListMatrix.ZipID,
ZipListMatrix.ListID" the query runs very slow (7.5 secs).

The table ZipListMatrix has 3.3 million records. I have indexes on ZipID,
ListID

When I do an "explain"...I see the dreaded: Using Temporary, Using Filesort

Can anyone help? I don't know what to do to speed up the query!
-bruce duncan
Jul 20 '05 #1
4 2496
Bruce D wrote:
But as soon as I change the order by to "order by ZipListMatrix.ZipID,
ZipListMatrix.ListID" the query runs very slow (7.5 secs).

The table ZipListMatrix has 3.3 million records. I have indexes on ZipID,
ListID

When I do an "explain"...I see the dreaded: Using Temporary, Using Filesort

Try creating a single index containing both ZipID and ListID rather than
depending on the separate indices.
Jul 20 '05 #2
Bruce D wrote:
I have the following query that runs quickly (0.5 secs):
...order by ZipListMatrix.ZipID

But as soon as I change the order by to "order by ZipListMatrix.ZipID,
ZipListMatrix.ListID" the query runs very slow (7.5 secs).


What happens if you ORDER BY ZipListMatrix.ListID, without ordering by
ZipID? I'm wondering if you've created an index on UserList.ListID, and
forgotten to create one on the same field in ZipListMatrix.

http://dev.mysql.com/doc/mysql/en/OR...imization.html

This page describes cases where the ORDER BY must use a filesort.
The first case sounds like yours: ordering by two different keys.

That page also has a few suggestions to speed up filesorts.
You could also try creating a RAMdisk on your server, and point the
"tmpdir" option for mysqld to use that as its sorting space.

Another idea: create another column in ZipListMatrix that is a sensible
concatenation of ZipID and ListID, create an index on it, and then order
by that. By sensible, I mean concatenate in such a way that an alpha
sort on the result gives you the order you want.

Regards,
Bill K.
Jul 20 '05 #3
"2metre" <2m****@xxxhersham.net> wrote in message
news:ck**********@hercules.btinternet.com...
Try creating a single index containing both ZipID and ListID rather than
depending on the separate indices.


I created an index, X1, on the ZipID and ListID fields. When I ran the
query...it was still very slow.

Query:
select ZipListMatrix.ZipListMatrixID, ZipListMatrix.ZipID,
ZipListMatrix.CarrierRouteID, ZipListMatrix.ListID, ZipListMatrix.Quantity,
List.DisplayOrder
from ZipListMatrix use index (X1)
join List
join UserList on List.ListID = UserList.ListID
on ZipListMatrix.ListID = List.ListID
where UserList.UserInfoID = 869
and ZipListMatrix.ZipID = 23112
order by ZipListMatrix.ZipID, ZipListMatrix.ListID

Explain:

UserList: ref: ListID,UserInfoID: UserInfoID: 5: const: 3: Using
where; Using temporary; Using filesort:
List: eq_ref: PRIMARY,ListID: PRIMARY: 10: UserList.ListID: 1: :
ZipListMatrix: ALL: X1: NULL: NULL: NULL: 3376287: Using where:

Why didn't it use the X1 index?
-bruce
Jul 20 '05 #4
>
Why didn't it use the X1 index?
-bruce


Got it figured out!

It didn't have anything to do with the ORDER BY...
it was my fault when writing the "ZipListMatrix.ZipID = 23112"...ZIPID is a
character field...so MySQL was trying to compare the character field with
23112.
I changed the query from:
and ZipListMatrix.ZipID = 23112
to
and ZipListMatrix.ZipID = "23112"

and the query was back to subseconds!

-bruce
Jul 20 '05 #5

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

Similar topics

4
by: Tryfon Gavriel | last post by:
Hi all I recently noticed when trying to optimise a major query of a chess website I am the webmaster of, that adding an order by for "gamenumber" which is a clustered index field as in for...
4
by: David Link | last post by:
Hi, Why does adding SUM and GROUP BY destroy performance? details follow. Thanks, David Link s1.sql: SELECT t.tid, t.title, COALESCE(s0c100r100.units, 0) as w0c100r100units,
2
by: David | last post by:
Hi, I have an order form which has a field 'ProductID'. This form has a button on each record to open a new form linked by ProductID. This new form is a continuous form and obviously, only...
6
by: Larry R Harrison Jr | last post by:
I have a database I'm designing in Access 97. I have a custom field in a query which looks in {Table of Documents} and shows them all. It then needs a "latest revision number," stored in another...
2
by: Viorel | last post by:
Adding new row with default values. In order to insert programmatically a new row into a database table, without direct "INSERT INTO" SQL statement, I use the well-known DataTable.NewRow,...
4
by: Ron | last post by:
Hi all, I've got a frmCustomer form (designed via access 2000 form wizard) that uses a qryCustomerName query (also designed by query wizard) ordered by customer last name, first name, mi. Of...
6
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214,...
14
by: Paul_Madden via DotNetMonster.com | last post by:
Basically I have a listbox to which I add simple STRING items- I have a progress bar which I increment whenever I populate another portion of the complete set of items I wish to add. What I observe...
1
by: 28490 | last post by:
Hi, In 10.2 when a query is run with GROUP BY it does not order the output by the grouped columns. In earlier versions the output was always sorted by the the grouped columns even though there...
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: 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)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.