MySQL default order depends on WHERE [duplicate]

Question

Possible Duplicate:
Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

I have a table like this:

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` mediumint(8) unsigned NOT NULL,
  `country` enum('AF','AX','AL') DEFAULT NULL,
  `number` tinyint(3) unsigned DEFAULT NULL,
  `sort_order` double unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `country` (`country`),
  KEY `id` (`id`,`country`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

I have a table for which I changed the default ordering like:

ALTER TABLE test_table ORDER BY sort_order ASC;

This table is never updated and no records are removed or added during its life. This all seems to work so if I use the folowwing query:

SELECT * FROM test_table LIMIT 10

It returns the 10 records in the right order.

And even if I use:

SELECT * FROM test_table WHERE num=3

it returns the results in the right order.

But if I do

SELECT * FROM test_table WHERE country='AX'

It will return the results in reversed order.

Can someone tell me how this can happen?

Answer 1

Specifying the ORDER BY on a table is just a help for the engine to speed up queries with the same order. It will not force mysql to always return the result with the same ordering.

Described in this: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

"ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later. "

So you must use the ORDER BY expression in your queries too.

Answer 2

I guess the default order of your index on country is DESC. Because of this, if this one is used you get the "wrong" Order and in all other cases it is different. Not sure if or how it is possible to specify the order of the index in mysql, but i think it is.

But still im not sure if you can rely on the order if you dont specify one. Just add the ORDER BY statement to all your queries.