Menu
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!
476
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
