Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why won't my query use the index on my datetime field?

Tags:

mysql

I have the following table:

CREATE TABLE 'tableA'(
 `col1` int(11) NOT NULL AUTO_INCREMENT,
  `col2` varchar(20) NOT NULL,
  `col3` int(11) NOT NULL,
  `col4` varchar(200) NOT NULL,
  `col5` varchar(15) NOT NULL,
  `col6` datetime NOT NULL,
  PRIMARY KEY (`col1`),
  UNIQUE KEY `col2,col3` (`col2`,`col3`),
  KEY `col6` (`col6`)
) ENGINE=InnoDB AUTO_INCREMENT=1881208 DEFAULT CHARSET=utf8

I have an index on col6, a datetime column. I have almost 2M rows in the table, and the dates range from 1/1/2007 to 11/27/2012.

When I run the following, it doesn't use my index:

EXPLAIN SELECT * FROM tableA ORDER BY col6 ASC


+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | tableA | ALL  | NULL          | NULL | NULL    | NULL | 1933765 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+

I tried converting the datetime field to an integer and converting the datetime to a unix timestamp. However, it still won't use my index. What am I missing? Why does the optimizer insist on sorting through lots of rows (in this case 1,933,765 rows) rather than use the index?

like image 787
user_78361084 Avatar asked Nov 28 '12 02:11

user_78361084


People also ask

Why is my query not using index?

The reason the non-clustered index is not used is because it is more efficient to select the single row using the unique primary key clustered index. You can't get any faster than that to select all columns for a single row (barring a hash index on an in-memory table). Save this answer.

Can we create index on datetime column?

we solved it by creating index on datetime column . yes , cluster index will give better performance over non cluster indexes because ultimately non cluster index use cluster indexes internally .

Can we index date field in MySQL?

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query.

Can timestamp be indexed?

Yep, it absolutely is recommended and normal to index the timestamp columns when you query on them! I index my timestamps when I know I will query/sort by that column often.


1 Answers

Since you are not selecting on anything based on the index to narrow the result set, using it would only incur additional work to lookup via point-lookup every each row in the primary table.

like image 70
Ray Avatar answered Oct 02 '22 15:10

Ray