MySQL Forums
Forum List  »  Performance

Re: MySQL MyISAM sorting query makes it slower
Posted by: Timothy Mifsud
Date: April 14, 2014 04:13PM

Hi Rick,

Thanks again for your prompt reply. I have currently come up with the below as the fastest query in the stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP1` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP1`(
var_PositionCode int unsigned,
var_StartRowIndex int,
var_PageSize int,
var_OrderBy varchar(50)
)
BEGIN
IF var_OrderBy = '' THEN
SET @OrderBy = 'B.GameNumber DESC'; /* This was previously 'Yr DESC' */
ELSE
SET @OrderBy = var_OrderBy;
END IF;

CREATE TEMPORARY TABLE tblGameNumbers(GameNumber INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO tblGameNumbers(GameNumber) SELECT DISTINCT gp.GameNumber FROM DB2.gameposition gp WHERE PositionCode = var_PositionCode LIMIT 1000;

SET @strSQL = CONCAT('SELECT * FROM DB1.games g INNER JOIN tblGameNumbers B ON g.GameNumber = B.GameNumber ORDER BY ',@OrderBy,' LIMIT ?,?');

SET @StartRow = var_StartRowIndex;
SET @PageSize = 28;

PREPARE stmt FROM @strSQL;
EXECUTE stmt USING @StartRow,@PageSize;
DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE tblGameNumbers;
END $$

DELIMITER ;

The DISTINCT is needed as I am using a PRIMARY KEY for the temporary table, and I cannot have violation constraints. I have tried to remove the PRIMARY KEY from tblGameNumbers but then it is a much worse scenario.

What the stored procedure does is, if it is an initial search, (i.e. no Orderby criteria) it will sort by 'B.GameNumber DESC'. This was previously 'Yr DESC'. The B.GameNumber DESC speeds things up and since the larger GameNumbers belong to more recent records, it suits me well.

This is what I get with EXPLAIN (when sorting on B.GameNumber DESC)

1, 'SIMPLE', 'B', 'index', 'PRIMARY', 'PRIMARY', '4', '', 28, 'Using index'
1, 'SIMPLE', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'B.gamenumber', 1, ''

As can be seen, this seems quite good, but despite this, it still sometimes (with different PositionCodes) takes a couple of seconds to see the results in the web page, so I do not know how to optimise that. Also, the user can then sort the results on different fields so that is the same issue as before. Could it be the DROP TEMPORARY TABLE at the end that slows it somewhat?

As regards using InnoDB, I have explained my reasons to Edwin, namely that when I tested it out, the queries were faster on MyISAM, and my application is mostly reading from the DB, with inserts occurring only once a week.

Thanks,
Tim

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL MyISAM sorting query makes it slower
1025
April 14, 2014 04:13PM


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.