Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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:

share|improve this answer
    
Thanks for the answer! Could you please also explain what do you mean by only for an index key? I never heard about that, could you provide any reference on MySQL Manual? –  Mahdi Feb 15 '13 at 12:39
    
@Mahdi Please see the PS section. If the answer is not clear, that book at the end is a bit more detailed :-). –  Marian Feb 15 '13 at 13:04
    
Thanks for the nice explanation, I'd still definitely go for reading the book as well, I pretty new on databases! Thanks again! –  Mahdi Feb 15 '13 at 13:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.