Re: Large table designissue
Posted by: Rick James
Date: October 29, 2009 10:34PM

The only way to make this run faster
select distinct subpid from transactions;
is to add

You have a lot of indexes. And none are "compound". Let's see some other slow SELECTs; there may be further suggestions. Are INSERTs going slower than you would like? Note that your indexes take up more disk (7GB) than the data (6GB).

There are a lot of BIGINTs; I suspect some of them don't need to be 'big' -- BIGINT takes 8 bytes; INT takes 4.

Since your buffer_pool is nearly 5GB, I hope you have at least 7GB of RAM?

This is probably redundant:
KEY `id_idx` (`id`),

Any EXPLAIN that says "ALL" will have to run through the entire 6GB of data. This gets put into your 5GB buffer_pool, effectively flushing out anything that was there. This means subsequent queries will be slow. My suggested INDEX would mean scanning ALL of that one index, but that is (currently) much less than the buffer_pool, so it would be less harmful.

Since you have an auto_increment, it is possible (but complicated) to effectively do certain ALTERs by copying the table over, a few rows at a time.
1. CREATE new table(s) with desired indexes, normalization, partitioning, whatever.
2. Change clients to look in two places for the data (using UNION or some ad hoc code).
3. Loop through, copying 100 rows from this table to the new one(s). Delete from old table as you go.

Immediately after step 2, the clients will find all the data in the old table.
During step 3, they will find data from two places.
After step 3, they will find all the data in the new table(s).

To clean up,
4. Change clients to look only at the new tables.
5. Drop the old table (which is empty now anyway).

For example
select distinct subpid from transactions;
would become
select distinct subpid from transactions
select distinct subpid from transactions2;

Recommend you practice on 100,000 rows copied from transactions to a test table.

Options: ReplyQuote

Written By
October 24, 2009 02:46AM
October 25, 2009 09:11AM
October 29, 2009 09:36PM
Re: Large table designissue
October 29, 2009 10:34PM
November 13, 2009 01:05PM
November 18, 2009 12:15AM
November 19, 2009 12:38PM
November 19, 2009 12:55PM
November 24, 2009 11:49PM
November 25, 2009 11:23AM

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.