MySQL optimization can be solved?

Question

I have a table containing data about "deals". Transactions are either closed or open, and are also marked with signs in the is_closed column. (which, or course, is indexed).

there are about 10,000 open lines and 10,000,000 "closed" lines. Each “open” line is updated approximately once per second. (Only indexed fields are updated here). When the line is "closed", it will no longer be updated. (only reading).

I never need to run select, which processes both open and closed rows, so the question is: Should I split the table into two tables (open and closed) with the same structure?

The ends of one table are that every second I run an update in a table of 10,000,000 rows. The disadvantages of the two tables are that there is a kind of duplication of code, and that when closing the "deals" I need to delete them from one table and add to the other.

Answer 1

I think you can partition your table by the status column, so logically there will be 1 table. The list of sections seems appropriate in your case. Then you can go ahead and redo the "closed" section, if necessary ...

Answer 2

If Ratio of Close vs Open => 1000 (as you mentioned), then it would be better to make two different tables.

You can avoid code duplication using polymorphism. You can make one abstract transaction base class called AbstractDeal , and then extend two specific classes, OpenDeal and CloseDeal . You can map these two specific classes to your tables, respectively.

Hope this will be the best solution.

Answer 3

Boolean (true and false, or 1 and 0) columns like yours are good in some situations, but if you ever find yourself indexing such a column, you probably crossed the line.

If the values ​​were distributed evenly (50% true and 50% false), MySQL would not even use an index if it were not a coverage index. The cost of finding each row at the secondary index, where most of the data set will be returned, is expensive, so MySQL will perform a simple table scan.

In your case, since you are requesting a smaller distribution (1% false), MySQL may actually use the index.

However, then you need to wonder why you should store so many true values ​​in an index that is not even used, but they slow down the indexes and just free space.

... REVISED ...

Instead, consider saving the external index as another table. Consider adding a table called open_deals with the following structure, where deal_id is the main key for both deals and open_deals:

deal_id ---------- 100 121 135 

To get your open trades, simply do the following:

 SELECT deals.* FROM open_deals STRAIGHT_JOIN deals ON deals.deal_id = open_deals.deal_id 

We use direct join, because we always know that we will connect from left to right, and we get rid of MySQL to think about it.

Since open_deals consists of only one indexed column, the index will act as a coverage index. On a properly configured, hard server, the index will be stored in memory, so the table will be very fast.

The join, internally, will be similar to using your original secondary index, but without the overhead of all unused values.

For best performance, make sure the new values ​​are added to the end of the open_deals table, or, in other words, all new values ​​must be greater than the last, but you do it anyway.

To open a deal, add it to the open_deals table and mark it closed, remove the identifier from the open_deals table.

The advantage is that you do not need to move records between tables to update other indexes (even worse with a clustered InnoDB index). The only index that is updated here is a small index in the open_deals table.

Answer 4

Divide them into two tables. I do not see any flaws:

• The disadvantages of the two tables are that there is code duplication

So what? You are focused on performance, at least lines of code.

• and that when closing the "deals" I need to remove them from one table and add to another.

A few lines of code. Some UPDATE procedures will be converted to INSERT / DELETE.

---

The pros are:

• You have one smaller index in both tables.
• Any composite index (including the open/closed flag) will be a bit narrower.
• And more importantly, all of your indexes in a high-traffic table will be much smaller.

Answer 5

As long as both tables are in the same tablespace, you won’t get anything from splitting or partial processing of the table — the advantages of code simplicity greatly help to keep it in one.

InnoDB will perform row-level locking, so you cannot close closed transactions by opening open ones.