Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql query dont't uses index when there are variables in WHERE

Tags:

mysql

I have this table:

CREATE TABLE `ClientesHora_copy` (
`dia` varchar(6) default NULL,
`hora` varchar(2) default NULL,
`sit` varchar(17) default NULL,
`nodo` varchar(6) default NULL,
`clientes` decimal(41,0) default NULL,
`segundos` double default NULL,
`llamadas` decimal(41,0) default NULL,
`fecha` datetime default NULL,
KEY `nodo_fecha` (`nodo`,`fecha`),
KEY `nodo` (`nodo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and this query:

SET @sitio= 'ABA000';
SET @horaini='2013-02-12 15:18:00';
SET @horafin='2013-02-12 20:36:00';     
    EXPLAIN SELECT nodo,sit,clientes,segundos,llamadas,fecha,hora,@horaini AS  horaini,@horafin AS horafin
        FROM `ClientesHora_copy`
        WHERE 
        nodo =@sitio
        AND
        fecha BETWEEN DATE_SUB(DATE_FORMAT(@horaini, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)
            AND DATE_SUB(DATE_FORMAT(@horafin, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)

I have this in explain

id  select_type  table               type    possible_keys  key     key_len  ref        rows  Extra        
------  -----------  -----------------  ------  -------------  ------  -------  ------  -------  -------------
 1  SIMPLE       ClientesHora_copy  ALL     (NULL)         (NULL)  (NULL)   (NULL)  2716460  Using where  

But if I dont use the @sitio variable (but use the @horaini, @horafin variables):

EXPLAIN SELECT nodo,sit,clientes,segundos,llamadas,fecha,hora,@horaini AS  horaini,@horafin AS horafin
        FROM `ClientesHora_copy`
        WHERE 
        nodo ='ABA000'
        AND
        fecha BETWEEN DATE_SUB(DATE_FORMAT(@horaini, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)
            AND DATE_SUB(DATE_FORMAT(@horafin, "%Y-%m-%d %H:00:00"), INTERVAL 7 DAY)

I get this:

id  select_type      table                type    possible_keys    key         key_len  ref       rows  Extra  
------  -----------  -----------------  ------  ---------------  ----------  -------  ------  ------  -------------
 1  SIMPLE            ClientesHora_copy  range   nodo_fecha,nodo  nodo_fecha  18       (NULL)       1  Using where  

Any idea of why Mysql doesnt use the index with the @sitio variable but it does with @fechaini and @fechafin?

Thanks!

like image 476
Alejandro Avatar asked Feb 22 '13 20:02

Alejandro


People also ask

Why index is not used in MySQL?

The Benefits and Drawbacks of Using Indexes in MySQLIndexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries – when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time.

Does MySQL use index for in query?

Introduction to MySQL indexesAn index is a data structure used to locate data without scanning all the rows in a table for a given query. Indexes help retrieve data faster. Indexes are not visible to the users. They help speed up queries that require a search.

Why MySQL does not pick correct index for few queries?

MySQL can only use an index for searches up to the first range. Removing the range requirement on log_type should improve performance, but you may get mixed results by adding it in later in an outer query. If you do this, you'll also have to remove log_type from the covering index.

Does MySQL use index automatically?

Mostly we create index when creating table. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed automatically by MySQL. In addition, you can add indexes to the tables which has data.


1 Answers

The most likely explanation is that column nodo is character datatype, and character_set_connection does not match the character set specified for the column.

If the column is defined with latin1 characterset, try:

WHERE nodo = CONVERT(@sitio USING latin1)

As a demonstration, with utf8, explain output shows no index available:

EXPLAIN SELECT t.* FROM mytable t WHERE t.foo = CONVERT(@foo USING utf8)
                                                                  ^^^^
id select_type table type possible_keys key    key_len ref    rows Extra        
-- ----------- ----- ---- ------------- ------ ------- ------ ---- -----------
 1 SIMPLE      t     ALL  (NULL)        (NULL) (NULL)  (NULL)    3 Using where

But with latin1, explain output shows index is available (and is used):

EXPLAIN SELECT t.* FROM mytable t WHERE t.foo = CONVERT(@foo USING latin1)
                                                                  ^^^^^^    
id select_type table type possible_keys key    key_len ref    rows Extra        
-- ----------- ----- ---- ------------- ------ ------- ------ ---- -----------
 1  SIMPLE     t     ref  t_ix          t_ix   13      const     1 Using where
like image 167
spencer7593 Avatar answered Oct 13 '22 19:10

spencer7593