MySQL defaults to WHERE

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 have changed the default order, for example:

 ALTER TABLE test_table ORDER BY sort_order ASC; 

This table is never updated, and no records are deleted or added during its lifetime. It all seems to work if I use the folowwing query:

 SELECT * FROM test_table LIMIT 10 

It returns 10 records in the correct order.

And even if I use:

 SELECT * FROM test_table WHERE num=3 

it returns the results in the correct order.

But if I do

 SELECT * FROM test_table WHERE country='AX' 

It will return the results in reverse order.

Can someone tell me how this can happen?

Answer 1

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

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

"ORDER BY allows you to create a new table with rows in a specific order. Note that the table does not remain in that order after insertion and deletion. This option is useful primarily when you know that basically you query rows in a specific order part of the time. By using this option after major changes to the table, you can get better performance. In some cases, it can simplify sorting for MySQL if the table is ordered which you want to order later. "

Therefore, you should also use the ORDER BY clause in your queries.

Answer 2

I assume that by default your country index is DESC. Because of this, if this one is used, you get a "wrong" order, and in all other cases it is different. Not sure if or how you can specify the index order in mysql, but I think it is.

But still I'm not sure if you can rely on the order if you do not specify it. Just add the ORDER BY statement to all your queries.

Answer 3

why don't you try this query:

 SELECT * FROM test_table ORDER BY sort_order ASC LIMIT 10; SELECT * FROM test_table WHERE num=3 ORDER BY sort_order ASC; SELECT * FROM test_table WHERE country='AX' ORDER BY sort_order ASC; 

Answer 4

The reason you see this behavior probably looks like this: since the WHERE filters country , MySQL uses the index on country to find the returned rows. Most likely, the rows of the index are stored sorted by country , and then by id (the primary key of the table). This means that the most efficient way for MySQL to retrieve rows is to read the rows referenced by the index in the order they appear in the index. Therefore, the order in which lines are displayed on disk does not matter.

MySQL provides syntax to indicate the order of the index, but is currently ignored :

The index_col_name specification may end with ASC or DESC. These keywords are allowed for future extensions to indicate an ascending or descending index value. They are currently being analyzed but ignored; index values ​​are always stored in ascending order.

You need to add ORDER BY clauses to your queries to ensure that the rows will be returned in the desired order. By the way, in any case, this is always the case; SQL does not guarantee that rows will be returned in any particular order, regardless of how the rows are physically stored (unless the ORDER BY specified).