MySQL Forums
Forum List  »  Newbie

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
Re: Improving time response in SELECTS and INSERTS
April 28, 2012 05:39PM


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.