471,084 Members | 1,041 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 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 2396
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Tryfon Gavriel | last post: by
6 posts views Thread by Larry R Harrison Jr | last post: by
2 posts views Thread by Viorel | last post: by
14 posts views Thread by Paul_Madden via DotNetMonster.com | last post: by

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.