Column order for better index

Question

let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, plus some other columns.

My question is does it make any sense to make the database columns in tbl_children like parent_id first, following by child_id? Because I will index them, and my WHERE clause should be some thing like WHERE parent_id = x AND child_id = y.

Again this is just an example. I just want to know if column orders could improve the performance on indexed columns or not?

Platform: MySQL, InnoDB, on Integer Values.

Answer 1

No, the column order in the table is not important at all for the performance of any related query or any additional index.

A table T1(a, b) is equivalent to T2(b, a), if the data inside is the same. The order of the columns matter only for an index key, because there the data is ordered after the first column of the key.

PS: let's say on table Table1 (a, b, c, d) an index is created the following way:

CREATE INDEX idx_Table1 ON Table1 (a, b);

This creates an index, which is a data structure additional to a table in the format of a balanced tree (B-Tree) that helps some queries run faster. The index key is that pair formed of the data from columns (a,b) concatenated.

For example a query like:

select a from Table1 where a = 1 and b = 2

would use the index idx_Table1, while a query like:

select a from Table1 where b = 1 and c = 1

would most probably wouldn't use it but would benefit from an index:

CREATE INDEX idx_Table2 ON Table1 (b, a);

Some references:

• The MySQL reference page for CREATE INDEX is here.

• How MySQL uses indexes: reference page.

• A nice book on indexes: Use The Index, Luke.