let say I have two tables like
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
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.
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);
The MySQL reference page for CREATE INDEX is here.
How MySQL uses indexes: reference page.
A nice book on indexes: Use The Index, Luke.