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: Rick James ()
Date: April 28, 2012 05:39PM

> I split the only table in 4 identical tables, one per server.

That's called "sharding".

> I usually send queries about 2 columns, both bigint(16). I created an index in both columns to improve the time response of the SELECTs, but this made that the inserts (with LOAD DATA INFILE) started to take a lot of time, from 1 second to more than 10 minutes per table after creating the indexes.

Too much hand waving. Please provide the SHOW CREATE TABLE.

> I use 4 SELECTs with the UNION command.

Cannot do a union across 4 servers. You can do it across 4 tables, or even 4 databases, but only on a single server. Well... Federated (or MariaDB's FederatedX) may let you do a UNION across multiple machines.

> to SELECT from the 4 tables at the same time

Let's see the SELECT you want to do.

> LOAD DATA INFILE

How many rows at a time? Is this a one-time task? Or do you load millions of rows per day?

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.