Optimizing ORDER BY - Returned data is not in the expected order

MySQL & MariaDB Other MySQL® Questions
1

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?

2

Hi,

You should not relay on order of the data in SELECT statement result set unless you use ORDER BY - depending on how MySQL decides to execute things results may be not what you expect.

add … ORDER BY col if you want result sorted and have that column second in the index for optimal performance.