Btukfyl

I've got a single query

SELECT r.id

     , r.account_id

     , r.name

     , r.bucket_id

     , r.description

     , r.development

     , r.created_at

     , r.priority

  FROM realms r

 WHERE r.account_id = 3;

I run it on two different tables with the same indexes, and one result is sorted by r.id and r.created_at (same order either way), and the other is sorted by r.name. How can this be?

Looking at it through the table inspector in MySQL Workbench, the indexes for both are:

+---------------------------+-------+-----+-----------------+

| key                       |Type   |Uni  | Columns         |

+ --------------------------+-------+-----+-----------------+

| PRIMARY                   | BTREE | YES | id              |

| realms_account_id_name_UQ | BTREE | YES | account_id,name |

| realms_account_id_IX      | BTREE | NO  | account_id      |

| realms_bucket_id_IX       | BTREE | NO  | bucket_id       |

+---------------------------+-------+-----+-----------------+

I thought it was the indexes that decided what order rows came in, and the screen doesn't even blink when I switch between the two. If the primary key for both of them is id why does one show results ordered by name?

If you don't have an ORDER BY clause in your SELECT, the system can do whatever it feels like. Period. Full stop.

Now, I will explain what probably happened.

First, the Optimizer will analyze the indexes, the datatypes, the statistics, etc, and decide how to execute the query. You can get a peek into this operation by doing EXPLAIN SELECT .... It will say which index it is likely to use.

I see two indexes that are reasonable -- the two beginning with account_id. Either one would be fine. Probably the Optimizer had slightly different statistics on the two machine, leading it to pick one index on one machine, and the other on the other.

Analysis of using INDEX(account_id, name). That index is an ordered list of the pairs of account_ids and names. On the machine where it used that index, it drilled down the BTree index to the first entry for account_id = 3, then scanned forward. This gave you the results ordered by name.

Analysis of using INDEX(account_id). InnoDB, in order to find the data, tacks the PRIMARY KEY columns onto each secondary index. So, that index is effectively INDEX(account_id, id). On the machine where it used that index, it drilled down the BTree index to the first entry for account_id = 3, then scanned forward. This gave you the results ordered by id.

A third possibility is common and is worth noting. If there are lots of rows with account_id = 3, the Optimizer would decide to eschew the index and simply read the data. Since the data is stored according to the PRIMARY KEY, it would, again, deliver the rows in id order (but for a radically different reason).