5
I currently have a table with about 6 million records that in turn perform a large amount of I/O operations, so in the design of the project I chose to use the Innodb instead of Myisam in Mysql, after all, the lock would be per page and not per table.
But I have a huge problem, the MAJORITY of the queries made in this table is through a ** date period (datetime). By concept I tried **to share it, but I came across this limitation of Innodb
What do you suggest to improve the performance of these queries? Considering that I have a very large hardware limitation?
Below is the structure of the table.
CREATE TABLE `sensores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`equipamento_id` int(11) NOT NULL,
`data_hora` datetime DEFAULT NULL,
`valor_primario` float(10,6) DEFAULT NULL,
`valor_secundario` float(10,6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_sensor_equipamento_idx` (`equipamento_id`),
KEY `data_hora` (`data_hora`),
CONSTRAINT `fk_sensor_equipamento_idx` FOREIGN KEY (`equipamento_id`) REFERENCES `equipamento` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3515782247 DEFAULT CHARSET=utf8;
The operation consists of numerous "sensors" writing equipment reading information in this table every 15 seconds.
Most querys made are similar to instruction
SELECT * FROM sensores WHERE data_hora BETWEEN ? AND ?
Can you detail a little more about the structure of this table and what kind of changes, insertions and exclusions it usually suffers? What kind of queries are made? Without this, it is difficult to give useful answers.
– Victor Stafusa
Innodb’s great asset are TRANSACTIONS, if you don’t use it, you don’t really have to choose Innodb. Myisam is much faster for consultation.
– Havenard
@Victor, I’m sorry for the lack of information, I edited the question and put the table structure.
– Mauro Alexandre
@Havenard, really, but I have a lot of writing too, and their response time is very important. In business it is difficult to tell which operation is more important.
– Mauro Alexandre