0

I'm creating a database for a very heavy messenger like Telegram ... I decided to use a different table for client-username and separate it from the clients table.

This is My 'client' table:

InnoDB (because we are going to have too many updates over select !)
ascii_general_ci

     ID       -> int (unsigned parimary auto-index)
     password -> char (64) (not-null ('char' get us better performance over 'varchar' since password-length is fixed-length (64) (because of hash))
     ...

This is my 'client_username' table:

MyISAM (because we are going to have too many SELECT over update !)
ascii_general_ci


     client_ID       -> int (unsigned unique)
     client_username -> char (16) (using 'char' for better performance duo to it's fixed-length type. also it's better to use 'char' type in MyISAM type)

I separated username because the client table is InnoDB and it's not good as MyISAM in SELECT. So I separated username into a table with MyISAM type. Here we have the best performance in searching for username, and also I did it because I want to choose 'char' type for my username and I heard 'char' type is faster than varchar nly in 'MyISAM' type. Am I right about all of these points?

  • "very heavy" -- Please provide some ball-park numbers. You are concerned about looking up username. Seems like that would be less than 50% of the activity? – Rick James Mar 15 at 22:49
3

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.
  • COUNT(*) without WHERE is 'instantaneous' in MyISAM.
  • 2-col 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 VARCHAR"

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 CHAR over VARCHAR.
  • In InnoDB, CHAR and 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.

  1. Receive the query, possibly across a WAN. (Up to milliseconds.)
  2. Parse the tokens in the query.
  3. Figure out which table is being used for each column named in the query.
  4. Open the tables.
  5. 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.
  6. 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.)

  1. Locate the record -- perhaps via an index, perhaps "next after" the last record fetched.
  2. 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.
  3. 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".
  4. 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.
  5. "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".
  6. 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.


General

  • 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 UPDATE or DELETE + INSERT.
  • 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.)

| improve this answer | |
  • so you telling me that it's better to use varchar for the username and i should put it in client table not separated table ? – Mehdi Mar 15 at 17:52
  • @Mehdi - Yes to both. – Rick James Mar 15 at 18:16
  • but im a programmer too ... and i think it's better for username to be char(16) ... because in this way, mysql knows that 2 '8-BYTE' must be checked in each username so it's just have 2 times check (8-BYTE)... but in varchar, length must be read first .... then it's depends on length ... check is not 8-BYTE - 8-BYTE ... check process is char-by-char and this is bad for performance ... – Mehdi Mar 15 at 18:50
  • @Mehdi - My background assembly language work on 8-, 12-, 16-, 24-, 32-, 36-, 60, and 64-bit architectures. And developing applications in as little as 8KB of RAM plus 8KB of ROM. One thing I learned decades ago is that optimizing the algorithm is much more productive than optimizing at a low level. – Rick James Mar 15 at 22:34
  • 1
    @Mehdi - I added a lot about MyISAM myths. – Rick James Mar 16 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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