Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.3, 5.5.31, 5.5(EOL)
-
Both Windows and Linux
Description
In a query I have a table with 7 million records, joined with another table with 300.000 records.
First table:
CREATE TABLE `data` ( |
`Unita` CHAR(8) NOT NULL DEFAULT '', |
`Fase` CHAR(20) NOT NULL, |
`TipoU` CHAR(2) NOT NULL, |
`ID` CHAR(15) NOT NULL DEFAULT '', |
`FSTD` FLOAT NOT NULL DEFAULT '0', |
INDEX `Main2` (`Unita`, `TipoU`, `Fase`) |
)
|
Second table:
CREATE TABLE `aggrs` ( |
`Fase` CHAR(20) NOT NULL, |
`Processo` CHAR(6) NOT NULL, |
`TipoU` CHAR(2) NOT NULL, |
`Unita` CHAR(8) NOT NULL, |
`Liv` CHAR(2) NOT NULL, |
`Ragr1` CHAR(8) NULL DEFAULT NULL, |
`Ragr1Descr` CHAR(25) NULL DEFAULT NULL, |
`Ragr1Nome` CHAR(25) NULL DEFAULT NULL, |
`Ragr2` CHAR(8) NULL DEFAULT NULL, |
`Ragr2Descr` CHAR(25) NULL DEFAULT NULL, |
`Ragr2Nome` CHAR(25) NULL DEFAULT NULL, |
PRIMARY KEY (`Fase`, `Processo`, `TipoU`, `Unita`, `Liv`), |
INDEX `Ragr2` (`Ragr2`) |
)
|
In the following query
SELECT STRAIGHT_JOIN SUM(FSTD) |
FROM aggrs ag USE INDEX (Ragr2) |
JOIN data pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) |
WHERE ag.Ragr2='DD4M'; |
the index Main2 is ignored when I use Aria engine, work fine with MyISAM. The explain plan show less than 1000 rows to read with MyISAM, and the full 7 million rows with Aria.
If you need I can upload on your ftp the dump file to recreate the test case (50MB bz2).
Thank you.