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