MySQL Autocomplete – Table Indexes for Autocomplete

As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )

EDIT

If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.

My rules of thumb for indexing are to

• first, read the execution plan,

then think about adding (or verifying) an index on

• every column used in a WHERE clause,
• every column used in a JOIN condition,
• every column used in an ORDER BY.

In some cases, one multi-column index will be faster in SELECT statements than several single-column indexes.

Then, make sure your WHERE clauses are sargable. Expressions like LIKE '%user: john.doe@example.com%' are not sargable. Unless you extract the user information and store it elsewhere, full table scans are in your future.

When MySQL has to use a filesort, it writes rows to the sort buffer using a fixed-width format. The wider your columns, the longer it's liable to take to sort.

Filesort uses a fixed-length row-storage format similar to that used by the MEMORY storage engine. Variable-length types such as VARCHAR are stored using a fixed length.

The fixed length for columns of type text defaults to 1024 bytes. MySQL will use a slower algorithm for sorts that include text columns.

[MySQL] normally uses the modified algorithm except when BLOB or TEXT columns are involved, in which case it uses the original algorithm.

[snip]

One problem with this approach is that it reads rows twice: One time when evaluating the WHERE clause, and again after sorting the pair values. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)