MySQL Performance: MySQL => Optimizing ORDER BY - Returned data is not in the expected order

Optimizing ORDER BY - Returned data is not in the expected order [message #2658]

Thu, 21 February 2008 07:18

arthurica
Messages: 7
Registered: January 2008

Junior Member

I have a weird problem... I have a very large table with products and I am trying to optimize some queries for retrieving data from this table

I run this query on the table

ALTER TABLE prod ORDER BY prod_rank DESC;

So when I retrieve data I expect to have the records sorted by prod_rank descending... This is happening for most of categories BUT not for few ones when data appears to by arranged randomly

I have tried it with 2 differnet indexes and the results are about the same

1. index RANK (fk_cat, prod_rank DESC)
2. index fk_cat (fk_cat)

So for example this query will return the results sorted just fine

select prod_key, prod_rank from products
where fk_cat = 100
limit 1000

but this one will not

select prod_key, prod_rank from products
where fk_cat = 250
limit 1000

This appears to be randomly as far as the category ID

Am I doing something wrong or am I missing somthine obviously?

Report message to a moderator