Correct indexing on a large table
I have a table with 8 million records that looks like this:
CREATE TABLE `bdp_dosis` (
`ID_INSTITUTION` varchar(4) NOT NULL default '',
`ID_SERVICE` char(2) NOT NULL default '',
`SECUENCIAL` char(3) NOT NULL default '',
`TYPE` char(1) NOT NULL default '',
`DATE_LECTURA` varchar(8) NOT NULL default '',
`ID_TARJETA` varchar(9) default NULL,
`DATE_ASIGNACION` varchar(8) NOT NULL default '',
`DOSIS_1` int(11) default NULL,
`DOSIS_2` int(11) default NULL,
`COMMENT` char(1) NOT NULL default '',
`CATEGORIA` char(2) default NULL,
`TYPE_TRABAJO` char(2) default NULL,
PRIMARY KEY (`ID_INSTITUTION`, `ID_SERVICE`,`SECUENCIAL`, `TYPE`, `DATE_LECTURA`, `DATE_ASIGNACION`, `COMMENT`)) ENGINE=MyISAM
The design of the table cannot be changed, but some indexes will make it less bad with this query:
select d.ID_SERVICE,d.SECUENCIAL,d.TYPE,d.DATE_LECTURA,d.DATE_ASIGNACION,d.DOSIS_1,d.DOSIS_2,d.COMCOMMENT
from bdp_dosis d
where d.ID_INSTITUTION='46C7'
and d.DATE_asignacion>'20080100'
and d.DATE_asignacion<'20081232' and locate(d.COMMENT,'WQ')<>0
order by d.ID_SERVICE,d.SECUENCIAL,d.TYPE,d.fecha_lectura desc
EXPLAIN command says:
id select_type table type possible_keys key key_len ref
1 SIMPLE d ref PRIMARY PRIMARY 4 const
rows Extra
254269 Using where; Using filesort
I tried to put the index in ID_INSTITUTION-ID_SERVICE
with no big results. Any idea?
source to share
It is more likely that the key in ID_INSTITUTION
and DATE_asignacion
help.
If it's not too far yet, you can look into mysql 5.1 and use partitioning .
source to share
Indexes work best on columns in a clause where
as they help filter the result. Ergo, indices must be ID_INSTITUTION
ANDed DATE_asignacion
.
However, these indexes will not help you much if you have a command locate
in your suggestion where
: this usually starts a table scan.
For speed purposes, I would also make DATE_asignacion an integer, since int comparisons are faster than string comparisons.
source to share