MySQL Forums
Forum List  »  Performance

Re: Some general questions
Posted by: Rick James
Date: December 14, 2008 03:29PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3230
December 14, 2008 08:45AM
Re: Some general questions
1514
December 14, 2008 03:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.