Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very large table where specific queries made by my application are timing out. The table looks something like this:

LargeTable
------------
Id [BIGINT(20)]
UtcTimestamp [BIGINT(20)]
Pivot [BIGINT(20)]
Details [Text]

I have an index over 1) Pivot 2) UtcTimestamp.

Now, the query I'm running looks something like this:

SELECT Id, Pivot, UtcTimestamp, Details FROM
LargeTable WHERE 
UtcTimestamp>=1380067200 AND UtcTimestamp<=1380153599 AND Pivot=2;

Since there is an index over the pivot and timestamp, this query should be blazing fast, but it doesn't appear to be. I ran an explain on this query expecting "using index", but instead I get "using index condition". Here is the explain:

+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table       | type  | possible_keys          | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | LargeTable  | range | pivot_and_timestamp_id | pivot_and_timestamp_id| 18      | NULL |  963 | Using index condition |
+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+ 

It's definitely selecting the right index, but why is this query not as fast as it should be?

The MySQL server version is 5.6.14.

share|improve this question
    
Whats the MySQL server version and how does the table look like? and show us all columns in the select please and not [more selects here] .... –  Raymond Nijland Oct 9 '13 at 18:04
    
And the used storage engine? –  Raymond Nijland Oct 9 '13 at 18:30
    
@RaymondNijland Innodb. Sorry I didn't explicitly state that, I thought it was implied from the tags –  Chris Oct 9 '13 at 18:32
    
You only get "Using index" when you write querys that select data from the index only Details isn't indexed so innodb needs to look that up in the table data (extra random I/O needed) if the innodb buffer doens't have this data.. –  Raymond Nijland Oct 9 '13 at 18:35
    
But Using index condition is pretty good because it finds the rangs off UtcTimestamp>=1380067200 AND UtcTimestamp<=1380153599 and checks the Pivot value here –  Raymond Nijland Oct 9 '13 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.