MySQL Forums
Forum List  »  Performance

Re: MySQL MyISAM sorting query makes it slower
Posted by: Timothy Mifsud
Date: April 14, 2014 02:10AM

Hi Rick,

Thanks for your reply.

I use DISTINCT as I am using a PRIMARY KEY (on tblGameNumbers(GameNumber) - the temporary table that I am putting the "LIMIT 1000" records in). Thus if I do not use DISTINCT it will in some cases violate the PRIMARY KEY constraint if >1 row will have the same GameNumber for the given PositionCode (even though this might be rare). 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?

I think it is not only how many PositionCodes there are, but also how sparsely located they are in the gameposition table. This is because I have tried the query with a PositionCode which appears most frequently and it is very quick - Could this be because it will find 1000 sooner?!

As for the compound index, 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...I am not too keen on adding indexes if not absolutely necessary as adding that index on a 400,000,000 row table would produce a massive increase in the .MYI file.

I do have an index on Yr in games.

What I did notice...If I place the following

(SELECT DISTINCT gp.GameNumber
FROM DB2.gameposition gp
WHERE PositionCode = 669312116 LIMIT 1000)

rows I am joining on into a physical table (tmp(GameNumber UNSIGNED INT NOT NULL PRIMARY KEY)) and run the following query:

EXPLAIN
SELECT *
FROM DB1.games g
INNER JOIN tmp B ON g.GameNumber = B.GameNumber
ORDER BY g.Yr DESC
LIMIT 0,28

the execution plan becomes much much better (no derived or filesort or temporary)...This resembles what I am doing in the actual stored procedure, except that in the stored procedure I am creating a temporary table (tblGameNumbers with the same primary key as tmp), inserting the 1000 matching GameNumbers there, and then joining as above.

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)? 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)...

Thanks,
Tim



Edited 1 time(s). Last edit at 04/14/2014 03:34AM by Timothy Mifsud.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL MyISAM sorting query makes it slower
943
April 14, 2014 02:10AM


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.