Improving time response in SELECTS and INSERTS

[Ivan Garcia]

Hello

I have one database to save a lot of records from 4 servers (about 300 millions of rcords).

To improve the performance I split the only table in 4 identical tables, one per server.

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. I improved a bit the LOAD DATA time response by increasing 'bulk_insert_buffer_size' variable, but not enough.

So i need some ideas to improve time response in SELECTS and INSERTS.

On the other hand, to SELECT from the 4 tables at the same time, I use 4 SELECTs with the UNION command. Is it possible to do it on other way? I have read something about JOIN and USING commands, but I haven't succeed.

Thanks in advance,
Ivan