Re: MySQL MyISAM sorting query makes it slower
Posted by:
Rick James
Date: April 14, 2014 12:23PM
> I do not know if I need the PRIMARY KEY constraint but I thought it would make the join (with DB1.games) faster. Is this right?
A PRIMARY KEY is a UNIQUE key is a KEY. When JOINing, the field(s) used for the JOINing make a difference. In the target table:
* No index -- must scan entire second table for _each_ field in first table;
* Non-UNIQUE KEY -- uses index, but scans forward to see if multiple rows match; this is only slightly worse than...
* UNIQUE/PRIMARY KEY -- a single BTree probe into second table.
For MyISAM, the BTree for the PRIMARY KEY is stored identically to how a UNIQUE index is stored -- as a BTree in the .MYI file.
For InnoDB, the PK is stored with the data, unlike UNIQUE keys, which are stored elsewhere.
Therefore, InnoDB _usually_ has a slight advantage over MyISAM in that the fetching of the row in the target of the JOIN requires one fewer block to be fetched from disk (or, usually, cache).
OK, the DISTINCT. But... Since it is rarely needed, it _may_ be faster to put it in the outside query, not the subquery. (This has the potential side effect of not giving you a full 1000 rows, but that seems noncritical for your use case.)
Suggestion: Manually try the SELECT with and without the DISTINCT. See what the timing is like; see how many rows you get each time. Then try it will the DISTINCT in the outer SELECT.
> I had tried one on gameposition(GameNumber, PositionCode) (i.e. with the columns inverted as to the one you suggested) and it had actually slowed things down
The order is critical! Think of it this way... Say you have INDEX(firstname, lastname), but the query is WHERE lastname='Mifsud' AND firstname LIKE 'T%'. That can't get to the `lastname` until it has satisfied firstname LIKE 'T%', which is probably a long list of entries.
> I do have an index on Yr in games.
But... Is it a "compound" index, like I suggested? Back to my example... With INDEX(firstname), INDEX(lastname), the optimizer will pick one of these methods:
* Use INDEX(firstname), find all firstnames beginning with T, then fetch all the rows to filter on lastname. Not efficient.
* Use INDEX(lastname) to find all the Mifsuds, then fetch all the rows and filter on T%. Possibly more efficient, but still not optimal
* Do an "index merge" -- Find all the T%, find all the Mifsuds, "merge" these lists, then finally go for the rows. This is clumsy because of all the temp tables needed.
OTOH, with INDEX(lastname, firstname), it can drill down the BTree to the first entry for Mifsud,T, then scan forward until no more T%. That is the perfect index.
> So my final question is: Does using a temporary table (tblGameNumbers) in the join produce a different execution plan than using a physical table (tmp)?
The EXPLAIN will probably be the same. However, implicit tables _may_ have less overhead. And implicit tables try to use MEMORY before automatically turning into MyISAM; this can be a significant speedup.
> As I cannot run an EXPLAIN with the temporary table and the following SELECT (as from what I gather you can only EXPLAIN one statement)...
I don't understand your comment. Granted, INSERT...SELECT is not EXPLAINable, but the SELECT in it is. An the INSERT is merely going to do a batch insert of the rows that the SELECT delivers. (OK, there is the question of whether the SELECT writes it all to disk, or whether the rows are "piped" to the INSERT. I don't know the answer.)
> SELECT *
When doing EXPLAIN, use the fields you will actually use, not "*". If you are using only a few fields, it may be able to use a compound index for greater efficiency; hence the EXPLAIN won't be reflecting the real query plan.
(And I agree with Edwin -- switch to InnoDB, unless you have a good reason not to.)