| 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?
|
|
|
| Re: Optimizing ORDER BY - Returned data is not in the expected order [message #2726 is a reply to message #2658 ] |
Fri, 14 March 2008 15:29  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
