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.

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) querying performance when table sizes get large and JOINs need to be done on them? E.g. should I put all of the bit/tinyint fields first?

What about most common queries? Could querying be faster if one were to front load the table with what might be the most commonly used columns?

Based on this (and/or other considerations), should I plan my table structure based on the field type, or is something like an alphabetical order just as good?

share|improve this question
    
The MySQL optimizer is cost based... if MySQL acces the wrong tables with JOIN in combination with ORDER BY / GROUP BY the query most likly need "using filesort" or "using temporary; using filesort" what will impact your query performance.. –  Raymond Nijland Oct 30 '13 at 21:59
1  
Hi, I'm not sure I understand your comment –  Drewdavid Oct 30 '13 at 22:45
    
Maybe ive commented the question wrong... i readed the combination JOIN/ORDER BY.. so ive comment something about the SQL planner of MySQL.. –  Raymond Nijland Oct 30 '13 at 23:13
    
I think you mean if the column order makes anny differance for query performance? column order isn't important i think, if MySQL optimizes the alignment (see en.wikipedia.org/wiki/Data_structure_alignment) of the SQL datatypes with the CPU/memory.. –  Raymond Nijland Oct 30 '13 at 23:17
    
And JOIN query should really be indexed for SELECT querys.. but indexing will slow down INSERT, UPDATES or DELETES an little bit so DONT index every column... well what can i say indexing is like ... art –  Raymond Nijland Oct 30 '13 at 23:18

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.