Re: MySQL query with timestamp not using index
Posted by:
Rick James
Date: April 15, 2014 03:21PM
> `c_ip` varchar(15) DEFAULT NULL,
IPv6 is upon us. You need VARCHAR(39) or BINARY(16) if you are willing to convert it.
It is generally not wise to make an InnoDB table without a PRIMARY KEY. The queries in question would possibly run faster if the PK were compound, starting with `timestamp`.
Most of the fields are NULL? Are they really optional?
> PARTITION P201612
Since the PARTITION code (sloppily) opens all the partitions for every operation, it is better not to create future partitions until necessary. Exception: the MAXVALUE partition.
> timestamp >= curdate() - interval 6 day
> partitions: P201404,P201405,P201406,...
Well, those agree. It did prune off the partitions before April.
How often is dams_tech = 'G' ? If it is infrequent, then this _may_ be beneficial:
INDEX(dams_tech, timestamp)
How many rows are there with (timestamp >= curdate() - interval 6 day) ?
If that is a large number, then the query is destined to take a long time, regardless of whether the index is used.
The optimizer avoids using indexes if it needs to touch more than, say, 20% of the rows. It is more efficient to simply do a table scan (or partition scan). This is probably the answer to your original question; much of the rest of what I am saying addresses performance of the query.
I believe (but am not sure) that the optimizer decides on a single query plan for all partitions. This effectively precludes using the index for your test case.
OTOH, if you added this, it would usually prune down to a single partition:
AND timestamp < now()
(or something that will catch all the rest of the rows, but not launch into the other partitions)
Will you be purging "old" data? If so, how long do you need to retain the data? You are set up to purge by months.
There are 200 million rows in the table?
Such a large table begs for having ids instead of VARCHARs. That is, "normalization" is desirable, and would probably speed things up by not having to do as much I/O.