0

I have 2 columns col1 and col2. Based on the accepted answer in here, I feel like I need 2 multi-index indices for my use-cases: index1 (col1, col2) and index2 (col2, col1). Right now I only have index1 (col1, col2) and the query of one of my use-cases is very slow. However, I am wondering if I create the 2nd index, would it negatively affect the performance of the use-cases that should use the first index, since SQL may choose the 2nd index to use instead? Thanks

Tommy Do
  • 51
  • 1
  • 6
  • There exists non-zero probability that after second index creation MySQL will select it instead of first index for some query. There exists non-zero (but very-very small) probability that this index selection won't be optimal for this particular query. In such rare case you may try to renew table statistic, and if this won't help than you may add a hint to ignore this index (or force another index) into this query. – Akina Nov 18 '20 at 16:42
  • Do not apply sql-server tips to MySQL (and vice versa). – Rick James Nov 21 '20 at 00:15
  • Please provide the table definition and query. – Rick James Nov 21 '20 at 00:16

2 Answers2

1

Creating the second index should not have an impact on the existing query. MySQL should be smart enough to choose the best index.

The second index will slow down insert/update/delete operations on the table, because it also has to be maintained.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

When using a bounding box for searching by latitude and longitude, it is quite appropriate and beneficial to have both

INDEX(lat, lng)
INDEX(lng, lat)

(But, without specifics, this advice may not apply to your code.)

Rick James
  • 135,179
  • 13
  • 127
  • 222