One of my order in the table has a multi-column index
1. Under normal circumstances, select the where colleagues behind with the index of the two fields, query quickly
2. But if I only query user_id this field will be particularly pretty, if combined with other judgment, basic card dead, in my impression, multi-column index, if can only use the first field is effective, the explain also shows effective

Want to know the cause of the slow query, hope to get your help, thank you

CodePudding user response:

1: in theory, if the column UserID high selectivity and equal distribution of data and use the index should be very fast
2: look from the execution plan, there are indexes, but Rows=300000 + explain the amount of data that is at least df_order table. Tens of millions of dollars not optimized, the best partition table. In addition, if the query conditions and other fields, can lead to back to the table, and an order of magnitude is slow

CodePudding user response:

reference 1st floor ybhcolin response:

1: in theory, if the column UserID high selectivity and data distribution uniformity should be very fast using the index
2: look from the execution plan, there are indexes, but Rows=300000 + explain the amount of data that is at least df_order table. Tens of millions of dollars not optimized, the best partition table. In addition, if the query conditions and other fields, can lead to back to the table, and an order of magnitude would slow

If I add single index to separate user_id field, will improve?

CodePudding user response:

Performance will not improve

CodePudding user response:

For large amount of data table don't count

CodePudding user response:

reference 4 floor out of the fish response:

for large amount of data in the table don't use the count

Don't count, there are other ways to replace, please find out the quantity?

CodePudding user response:

Time can count and record down, count commonly used as a statistical use, should not be used frequently

CodePudding user response:

The index is how set up? Which is the primary key?