The MyISAM vs InnoDB Myth
"InnoDB is not good as MyISAM"
That's an old wives' tale. Erase it from your mind.
- InnoDB has improved a lot since that rumor was started.
- You now have user information split across two tables; the small overhead of doing such is probably worse than having all the info neatly together in one InnoDB table.
Bottom line: Use InnoDB for all tables. There are very few exceptions to this simple rule. In no particular order:
- InnoDB tables usually have a 2x-3x larger disk footprint. But, so what, disks are huge.
WHERE is 'instantaneous' in MyISAM.
AUTO_INCREMENT -- standard in MyISAM; clumsy to simulate in InnoDB. (Rarely asked for.)
- Performance in obscure cases. (No specifics come to mind at the moment.)
- One might quibble that the differences in
FULLTEXT constitute an issue.
On the flip side, Oracle has taken the stand that MyISAM will be removed from MySQL.
The CHAR vs VARCHAR Myth
CHAR is better than
Another _old_wives' tale. Even in MyISAM, that quote is often taken out of context.
- Even in context it is rarely valid
- If you have variable length data, the savings for I/O is higher than the alleged savings of
- In InnoDB,
VARCHAR are mostly implemented identically.
Bottom line: Use
CHAR only for strings that are truly fixed length.
The need-to-optimize-the-little-things Myth
I'll start with the 'answer' first.
Even before looking at the data, there are other tasks.
- Receive the query, possibly across a WAN. (Up to milliseconds.)
- Parse the tokens in the query.
- Figure out which table is being used for each column named in the query.
- Open the tables.
- Invoke the Optimizer to deduce the best way to perform the query. This will involve locating all the possible indexes, doing probes into the tables to gather statistics, etc.
- Run the query.
In the grand scheme of things, locating a record is far more costly than anything that is done with the record. (This is a generalization, not an absolute.)
- Locate the record -- perhaps via an index, perhaps "next after" the last record fetched.
- Fetch the block containing the record. This is probably cached in the buffer pool, but it might need to be fetched from disk. So, this step might be nanoseconds, or it could be milliseconds.
- Dissect the block to find the row in question. This might include scanning the "history list" if multiple transactions are running and the "isolation mode" needs to be consulted to decide which copy of the row is "visible".
- Now that you have the row, the columns need to be picked apart -- even with off-word-boundary issues, byte scans,
NULL checks, length checks (eg, for
VARchar, etc, we are talking nanoseconds per column.
- "Endianness" slips in about here. MySQL can handle big-endian and little-endian hardware architecture with binary compatibility. This implies that for some hardware-dependent situations, it must swap bytes to get the column value into the right "endianism".
- Do something with the column. This may be simply copying it intact; it may be applying a function (collation, summation, sqrt, whatever). Again nanoseconds.
Now, what was your question? Oh, yeah, you were concerned about some tiny part of the last step.
- Word boundaries, for various hardware, software, and design reasons are not worth thinking about.
- For large tables, I/O is a much bigger factor in performance than fixed versus variable-length things.
- Fixed length in MyISAM had very few advantages. Most vanish when you aren't doing
- All columns in a row needed to be "fixed", else it was "variable".
- InnoDB possibly has zero benefits from "fixed".
- MyISAM was designed before variable-length charsets (utf8, etc) were added. Even
CHAR is effectively variable length when using utf8.
(And stop reading any MySQL reference that is over a decade old.)