Please, some directions,

Have a list of goods, have the status 0 1 2 represent a state of goods 1 normal 2 0 audit failure,
Due to do the cloud library goods, for others to collect call, so all the data to demonstrate the basic need status=1.

There are a few small screening project, I now do is index is as follows:


All the indexes of basic status, but because the library is millions of tens of millions of library, this effect is a bit poor, poor under the condition of high concurrency,

What are the members of relevant design experience can tell!

I now idea is to build a history of the library, library to put the failure in history, because of the failure of the basic query, but I had trouble with two tables, one table can pass appropriate indexes to solve this problem, please correct me big!!!!!

CodePudding user response:

The distribution of index to combine data and specific query SQL to design, your these information, also can't see your index design reasonable not reasonable,

IO index is to reduce the effect of reading data, you should according to this point with your specific SQL and table data to design index,

Me your index are all status at the beginning of, this is not a right way to do it, completely the K_SELLER index, for example, I estimate the status=1 data should be not a few, you change the location of the index columns here, K_SELLER (sellid, status), if sellid is selective high column, so in the same status=1 and sellid=ABC conditions, K_SELLER (sellid status), first by sellid reduces the index scan range, status, or the actual situation sellid is a better choice, I mean here is to pay attention to the order of the index columns,

Your question involves too much information, not personally to the actual environment, it is difficult to get the best solution,

On establishing a library history, if you can handle code logic, reduce the amount of data table is always good for query,

CodePudding user response:

How goods table has tens of millions of data? Why is there seller salesman properties and goods list? I understand you this may be a selling goods table, there is a strong correlation with date, can adopt the way of the partition table to deal with,

CodePudding user response:

This depends on how do you write your statement, the index is in the service of the statement,

CodePudding user response:

refer to the second floor stelf response:

goods table how can have tens of millions of data? Why is there seller salesman properties and goods list? I understand you this may be a selling goods table, there is a strong correlation with date, can adopt the way of the partition table,

Do taobao passenger coupon information, the release of the goods on a regular basis, coupons expired on goods to failure, the goods fail is a few days time, failure of temporary didn't do, so the goods on the selection of state status

CodePudding user response:

All the data to demonstrate the basic need status=1, the index also?? May effect might as well go as fast as a full table scan, index allows you to quickly query to the results,
You try to look at the explain statement, see the specific index usage

CodePudding user response:

refer to ride the waves in fifth floor may reply:

basic all data to demonstrate need status=1, the index also?? May effect might as well go as fast as a full table scan, index allows you to quickly query to the results,
You try to look at the explain statement, see the specific index usage

The original poster can go to check, whether you need gender field index, the principle of this status is not set up index is consistent with you

CodePudding user response:

refer to ride the waves in 6th floor to reply:

Quote: refer to ride the waves in fifth floor may reply:

All the data to demonstrate the basic need status=1, the index also?? May effect might as well go as fast as a full table scan, index allows you to quickly query to the results,
You try to look at the explain statement, see the specific index usage

The original poster can go to check, whether you need gender field index, principle and the status index is not you agree

Explain, with the index, not to establish the status for the index, full burst, because the failure of many goods, we only need to query without failure for goods, but still not perfect, it seems to do failure history table is a good method