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!
解决方案
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