Text Columns should be moved towards end?

Question

Is there any difference between speed of fetching data if dynamic rows is moved towards end.

example : int , int , int , text is better than int int text int ?

my lead has informed me about this fact , how ever no such information is getting from internet ? please help ?

Answer 1

This is due to data alignment. INT takes 4 bytes, so 32-bit (equivalent to 4 bytes) processors will work with data in 4 byte sequences. Being able to pull off the data in an order that best suits the processor will lead to faster results and better performance.

Reference: http://en.wikipedia.org/wiki/Data_structure_alignment

Now, this is a problem often found in transferring data over a communication channel (because certain compilers will typically optimize your data structure by adding a reserved byte to 4-byte align certain members). However, MySQL also takes 4-byte alignment into account with NDBCLUSTER engines. This means that by placing your TEXT structure in between the INT values, you force more data retrieval than necessary.

Thus:

INT INT INT INT TEXT will process faster than INT INT TEXT INT INT as the processor can retrieve 16-bytes (the four INT) immediately without worrying about the size of TEXT.

For more details, see the MySQL documentation:

http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

Answer 2

TEXT and BLOB data are not stored in the same space as the column itself. They're stored in a special area reserved for this purpose.

These columns are always slower to retrieve, but the amount of slow-down varies considerably depending on your system tuning and data load. Sometimes it's irrelevant, and sometimes it results in serious thrashing. Just be advised that you should use a short VARCHAR field over a TEXT field if you can manage it, that the vastly longer length of the blob fields does not come for free.

That being said, TEXT columns are stored in the row as pointer to the actual data which makes them significantly smaller than VARCHAR in most circumstances. If you don't select them, they don't get loaded, and you don't incur the additional seeking required to assemble their data.

To be sure this applies to your version of MySQL and your tuning, build two large tables full of representative data and benchmark it yourself.