Fields after "ORDER BY" or "WHERE" and index in MySQL

Question

Do Fields after "ORDER BY" or "WHERE" might have index (PRIMARY, UNIQUE, INDEX) in mysql?

Consider a table with the following columns:

ID | AddedDate | CatID | Title | Description | Status | Editor

In these queries, are ID, AddedDate and CatID might have index?

  SELECT * 
    FROM table WHERE ID = $id


 SELECT * 
    FROM table 
ORDER BY ID

  SELECT * 
    FROM table 
ORDER BY AddedDate

  SELECT * 
    FROM table 
ORDER BY CatID

Answer 1

You can order by any field. Please clarify our question if you want to know more / something else.

You might want to read ORDER BY optimization. There it says that fields with index might even improve the sorting as no extra has to be done (in the optimal case).

Update:

Yes, you can add an index if you want (if this is what you mean, it is still not clear as OMG Ponies points out). In general it is to say that you should add an index to those fields that you often use in WHERE clauses.

Answer 2

As far as I know, there are three basic ways to order rows:

1. In-memory sort: Read all rows into memeory and sort them. Very fast.
2. Using sorted index: Read one row at a time, looking up the columns that are not in the index in the base table.
3. File sort: Build a sort order by reading a part of the table at a time. This is really slow.

For tables that fit in memory, MySQL will probably choose option 1. That means it won't use an index even if it's present. The index will just be overhead.

But indexes shine for bigger tables. If the table is too big for memory, MySQL can avoid the painful file sort and rely on the index.

These days, memory is plentiful, and tables almost always fit in memory. I would only add indexes for ordering after I saw a file sort happening.

Answer 3

Hi -

One of the main benefits of having an index that it lets you select only that subset of rows you're interested in. The alternative to using an index is to do a "full table scan".

Unless you have a "where" clause, you're not really going to get much benefit from having indexes.