Re: Improving time response in SELECTS and INSERTS
Posted by: Ivan Garcia
Date: May 07, 2012 07:17AM
Sorry for not answering before, but i were out on holidays.
Firstly i want to clarify one thing. It is only one mysql server with one database, where information of 4 servers are loaded every 10 minutes. At the begining, i created only one table for the 4 servers information, but i thought that 4 tables of 70 millions of records would have a better performance than 1 table of 300 millions of records, so i created 4 identical tables in the database, so each machine has his table for loading information.
4 identical tables: Castellana_Cdrs, Cerda_Cdrs, Guipuzcoa_Cdrs and Moraleja_Cdrs
| Moraleja_Cdrs | CREATE TABLE `Moraleja_Cdrs` (
`Num_A` bigint(16) unsigned NOT NULL,
`Num_B` bigint(16) unsigned NOT NULL,
`Status` enum('Entregado','Expirado','Borrado') DEFAULT NULL,
`Fecha_IN` datetime DEFAULT NULL,
`Fecha_OUT` datetime DEFAULT NULL,
KEY `Index_NumA` (`Num_A`),
KEY `Index_NumB` (`Num_B`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
Keys are now disabled to be able to load data.
Every 10 minutes about 60.000 records are loaded per table. And once per day, records are deleted to store only the last 15 days:
delete from Moraleja_Cdrs where Fecha_OUT<subdate(now(),15);
The problem is that with the indexes enabled it takes too long to load data (more than 10 minutes per table, and i receive the 4 servers information every 10 minutes), and with the indexes disabled the selects take about 3 minutes.
I use this select, for example:
(select * from Moraleja_Cdrs where Num_A=XXXX) UNION (select * from Castellana_Cdrs where Num_A=XXXX) UNION (select * from Guipuzcoa_Cdrs where Num_A=XXXX) UNION (select * from Cerda_Cdrs where Num_A=XXXX);
I search by Num_B too.
Thanks in advance,