InnoDB in 5.6 ULTRA FAST?

[Francisco Pastrana]

Hello!

I already installed MySQL 5.6 in one laptop just for tests. in the same computer i have MySQL 5.5 and one table with 59 million rows, I migrated the same table to 5.6 in the same computer, so I have two MySQL in the same computer.

Is this possible or can be done?

I used to run the same query in the two versions, first in 5.5 and the result delayed 6min 46.90 sec. then in 5.6 and took just 39.06 sec. to return the same result.

My test is correct? or the cache of 5.5 worked in 5.6?

here is the table and querys.

general_domi_v5 | CREATE TABLE `general_domi_v5` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`CLIENTE` bigint(20) unsigned DEFAULT NULL,
`FECHA` date DEFAULT NULL,
`SEGMENTO` char(15) DEFAULT NULL,
`SUBSEGMENTO` char(40) DEFAULT NULL,
`SEG_PLAN` char(12) DEFAULT NULL,
`NATURALEZA` char(7) DEFAULT NULL,
`INTERNO` tinyint(3) DEFAULT NULL,
`CTE_FACT` bigint(20) unsigned DEFAULT NULL,
`CVE_TPER` tinyint(3) unsigned DEFAULT NULL,
`TPER` char(3) DEFAULT NULL,
`MEDIO` char(4) DEFAULT NULL,
`NUM_TX` smallint(6) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FECHA` (`FECHA`,`SEGMENTO`,`NATURALEZA`,`INTERNO`,`TPER`),
KEY `F_SEGPLAN` (`FECHA`,`SEGMENTO`,`SEG_PLAN`,`NATURALEZA`,`INTERNO`,`TPER`),
KEY `FECHA_2` (`FECHA`,`SEGMENTO`,`SUBSEGMENTO`,`NATURALEZA`,`INTERNO`),
KEY `FECHA_3` (`FECHA`,`SEGMENTO`,`INTERNO`,`TPER`),
KEY `NUM_TX` (`NUM_TX`),
KEY `CLIENTE` (`CLIENTE`),
KEY `CTE_FACT` (`CTE_FACT`)
) ENGINE=InnoDB AUTO_INCREMENT=74903766 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=92


In MySQL 5.6 This is the screen:
mysql> SELECT 'PATRIM_DEBITO_INTERNO' AS CONCEPTO, count(DISTINCT cliente) AS CLIENTES
-> FROM general_domi_v5
-> WHERE FECHA BETWEEN '2012-10-01' AND '2012-10-30'
-> AND segmento = 'PATRIM' AND NATURALEZA = 'DEBITO' AND INTERNO = 1;
+-----------------------+----------+
| CONCEPTO | CLIENTES |
+-----------------------+----------+
| PATRIM_DEBITO_INTERNO | 5288 |
+-----------------------+----------+
1 row in set (39.06 sec)

Later I ran the same query again in 5.5 and in the same time I ran a similar query but with a date range longer than the first query, In MySQL 5.6 this is the screen:

mysql> SELECT 'PATRIM_TOTAL_INTERNO' AS CONCEPTO,count(DISTINCT cliente) AS CLIENTES
-> FROM general_domi_v5
-> WHERE FECHA BETWEEN '2012-05-01' AND '2012-11-30'
-> AND segmento = 'PATRIM' AND INTERNO = 1;
+----------------------+----------+
| CONCEPTO | CLIENTES |
+----------------------+----------+
| PATRIM_TOTAL_INTERNO | 10600 |
+----------------------+----------+
1 row in set (7 min 18.06 sec)

and in MySQL 5.5 this is the screen

mysql> SELECT 'PATRIM_DEBITO_INTERNO' AS CONCEPTO, count(DISTINCT cliente) AS CLIENTES
-> FROM general_domi_v5
-> WHERE FECHA BETWEEN '2012-10-01' AND '2012-10-30'
-> AND segmento = 'PATRIM' AND NATURALEZA = 'DEBITO' AND INTERNO = 1;
+-----------------------+----------+
| CONCEPTO | CLIENTES |
+-----------------------+----------+
| PATRIM_DEBITO_INTERNO | 5288 |
+-----------------------+----------+
1 row in set (12 min 50.92 sec)


Please don't be concerned for the long delays of time, this table is a bit bad designed, and the computer i'm using for test is a bit slow.

Any advice will be very appreciated!