Re: Some general questions

[Rick James]

Maybe. It depends. Sometimes.

If your index can go exactly to the row(s) needed, the vertical partitioning is probably not useful.

On the other hand,

1. If picking the rows to return is complicated, especially if it involves WHERE clauses hitting multiple joined tables, the vertical partitioning can speed things up a bunch. The idea here is to trick it into _not_ carrying around the TEXT/BLOB from rows that it has not yet eliminated (via WHERE).

2. If the query needs to do a "range scan" over the table with the TEXT (and especially if you are I/O bound) but don't need all the rows, then the TEXT is clumsy to step over.

You gave 3 suggested SELECTs:
* They are all wrong?? You meant t1.name, not t2.name?
* Presumably you have an index on 'name'.
* The first two are identical -- "comma" and "JOIN" are synonyms. ("LEFT JOIN" is something else.)
* The third case (... FROM (SELECT...) ) can be much worse, or can be much better -- need the real case to decide its merits.

ORDER BY ... LIMIT ...
This is where it becomes likely that the query plan will haul the TEXT fields around unnecessarily.
* If the WHERE is simple, AND the INDEX can use the ORDER BY, many approaches work efficiently.
* If the ORDER BY cannot be folded into the INDEX search, then efficiency is likely to suck.

With 10 out of 500K, it will be easy to see whether things are poorly done. Either experiment or provide more details. Maybe we can make a likely guess.

Is your data bigger than RAM? SHOW CREATE TABLE (need to know engine, etc). SHOW TABLE STATUS (size). EXPLAIN SELECT (for various options). Cache sizes? Etc.