In the original table, endDate has an index, and the execution plan uses the endDate index. I wanted to add a composite index, and initially created the index in the order of partyId, aiipCompanyCode, status, startDate, endDate. However, upon checking the execution plan again, it still chose the endDate index. After rearranging the composite index to status, partyId, aiipCompanyCode, startDate, endDate, it used the newly created index. When looking at the data, the partyId field has a good degree of distinction. Why is it that when placed in the leftmost column, the index is not used?
Please provide the result of EXPLAIN ANALYZE. A statement can only use one index by default, and the query optimizer might have chosen a different index.
TiDB indexes follow the leftmost matching principle; otherwise, the index cannot be used. For the case of two columns with OR conditions, you need to enable index merge to use the indexes on both columns. Therefore, the index created on partyId, aiipCompanyCode, status, startDate, and endDate cannot use a single index because partyId and aiipCompanyCode are associated with OR conditions. For the index on status, partyId, aiipCompanyCode, startDate, and endDate, it is very likely that only the first column status is used. The above is just speculation; you can provide the execution plan after creating the index for more details.
The main reason is that the number of connections is too high, causing the CPU to be fully utilized. You can try to reduce the number of connections or increase the CPU resources.
The screenshot is incomplete. Please export the statistical information and upload the table structure to facilitate reproduction in the test environment. Thanks.
|
username: 胡杨树旁
|
username: 
|
username: 
|
username: