GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.

    Comment

    Working...
    X