MySQL Forums
Forum List  »  Stored Procedures

Very slow query
Posted by: Bill Gladstone
Date: September 23, 2013 12:21PM

A simple query of a table that contains about five million records is taking several minutes to execute. How can I make the query run faster?

The schema looks like this:

DROP SCHEMA IF EXISTS `TwitterCollections` ;
CREATE SCHEMA IF NOT EXISTS `TwitterCollections` DEFAULT CHARACTER SET utf8mb4 ;
USE `TwitterCollections` ;

DROP TABLE IF EXISTS `TwitterCollections`.`Statuses` ;
CREATE TABLE IF NOT EXISTS `TwitterCollections`.`Statuses` (
`StatusID` BIGINT NOT NULL ,
`CollectionSearchTerm` VARCHAR(128) NOT NULL ,
`CollectionDateUtc` DATETIME NOT NULL ,
`ScreenName` VARCHAR(32) NOT NULL ,
`DateUtc` DATETIME NOT NULL ,
`Text` TEXT NOT NULL ,
`RawStatusJson` TEXT NOT NULL ,
PRIMARY KEY (`StatusID`) ,
INDEX `CollectionSearchTerm` (`CollectionSearchTerm` ASC) ,
INDEX `DateUtc` (`DateUtc` ASC) )
ENGINE = InnoDB;


And here is the slow query. (It's actually wrapped in a stored procedure, hence the "xxParam" variable names.)

SELECT * from Statuses
WHERE CollectionSearchTerm = collectionSearchTermParam
AND DateUtc >= minimumDateUtcParam
AND DateUtc <= maximumDateUtcParam;


"EXPLAIN" tells me that only one index is being used:

+----+-------------+----------+-------+------------------------------+---------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+------------------------------+---------+---------+------+-------+------------------------------------+
| 1 | SIMPLE | Statuses | range | CollectionSearchTerm,DateUtc | DateUtc | 5 | NULL | 95766 | Using index condition; Using where |
+----+-------------+----------+-------+------------------------------+---------+---------+------+-------+------------------------------------+


I've tried several other indexing schemes, including a composite of DateUtc with a hashed CollectionSearchTerm, but nothing seemed to work. Rather than my going into the details of my failed fixes, can someone tell me a good index that will make the above query run fast, or point out other errors I might have made?

Thanks!



Edited 1 time(s). Last edit at 09/23/2013 12:23PM by Bill Gladstone.

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow query
1959
September 23, 2013 12:21PM
1064
September 23, 2013 01:43PM
1063
September 23, 2013 03:38PM
1178
September 24, 2013 09:52AM
1044
September 28, 2013 03:26PM


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.