Skip navigation links

MySQL Forums :: Newbie :: Improving time response in SELECTS and INSERTS


Advanced Search

Re: Improving time response in SELECTS and INSERTS
Posted by: Ivan Garcia ()
Date: May 07, 2012 07:17AM

Hello,

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,
Ivan

Options: ReplyQuote


Subject Written By Posted
Improving time response in SELECTS and INSERTS Ivan Garcia 04/27/2012 04:34AM
Re: Improving time response in SELECTS and INSERTS Rick James 04/28/2012 05:39PM
Re: Improving time response in SELECTS and INSERTS Ivan Garcia 05/07/2012 07:17AM
Re: Improving time response in SELECTS and INSERTS Rick James 05/08/2012 09:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.