Adding Order by slows my query!

[Bruce D]

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

select ZipListMatrix.Z ipListMatrixID, ZipListMatrix.Z ipID,
ZipListMatrix.C arrierRouteID,Z ipListMatrix.Li stID,
ZipListMatrix.Q uantity,List.Di splayOrder
from ZipListMatrix
join List
join UserList on List.ListID = UserList.ListID
on ZipListMatrix.L istID = List.ListID
where UserList.UserIn foID = 869
and ZipListMatrix.Z ipID = 23112
order by ZipListMatrix.Z ipID

But as soon as I change the order by to "order by ZipListMatrix.Z ipID,
ZipListMatrix.L istID" 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

[2metre]

Bruce D wrote:

But as soon as I change the order by to "order by ZipListMatrix.Z ipID,
ZipListMatrix.L istID" 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.

[Bill Karwin]

Bruce D wrote:

I have the following query that runs quickly (0.5 secs):
...order by ZipListMatrix.Z ipID

But as soon as I change the order by to "order by ZipListMatrix.Z ipID,
ZipListMatrix.L istID" the query runs very slow (7.5 secs).

What happens if you ORDER BY ZipListMatrix.L istID, 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.

[Bruce D]

"2metre" <2m****@xxxhers ham.net> wrote in message
news:ck******** **@hercules.bti nternet.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.Z ipListMatrixID, ZipListMatrix.Z ipID,
ZipListMatrix.C arrierRouteID, ZipListMatrix.L istID, ZipListMatrix.Q uantity,
List.DisplayOrd er
from ZipListMatrix use index (X1)
join List
join UserList on List.ListID = UserList.ListID
on ZipListMatrix.L istID = List.ListID
where UserList.UserIn foID = 869
and ZipListMatrix.Z ipID = 23112
order by ZipListMatrix.Z ipID, ZipListMatrix.L istID

Explain:

UserList: ref: ListID,UserInfo ID: 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

[Bruce D]

>

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.Z ipID = 23112
to
and ZipListMatrix.Z ipID = "23112"

and the query was back to subseconds!

-bruce