How to index a MySQL table when both> = and <= are used?

Question

Say you have a query like

 select ID, REGION, START, END from COORD_SYSTEM where REGION=? and TYPE=? and START >= ? and END <= ?; 

And let this table have about 50,000 rows. The REGION column has 500 different values, and the TYPE column has 50 different values. The ID column is the primary key.

What would be the best way to index a table? I'm not quite sure if the coverage index was able to get due to the> = and <= icons. Here are a few options:

• create an index on COORD_SYSTEM (REGION, TYPE)
• create an index on COORD_SYSTEM (REGION, TYPE, START)
• create an index on COORD_SYSTEM (REGION, TYPE, START, END)

Update - here is the explanation:

  id: 1 select_type: SIMPLE table: COORD_SYSTEM type: range possible_keys: indx_A key: indx_A key_len: 50 ref: NULL rows: 590 Extra: Using where 1 row in set (0.00 sec) 

Answer 1

There is no reason why you cannot use the coverage index with range operators. The challenge (for uncoated indexes) is that the optimizer might think that a full crawl might result in fewer page views if your range is large and the index will not be used for some of your queries. Similarly, for some parameter values, the optimizer may choose to perform a full scan if the coverage index is not much better than scanning some sets of parameters.

So, given the description in the question, it is actually impossible to give the optimal solution for all cases.

What I usually do with such things is:

• Create a copy of the database
• Guess which index can do the job, and create that index.
• EXPLAIN multiple queries with ranges of different sizes (for more ranges, more I / O is required to return to table data if you have not covered the query, so you should try for general sizes and range levels)
• Drop the index and try a different one, possibly with a different coverage index with columns in a different order

You might even want to create two or more coverage indexes with fields in different orders if you run this query much more often than the corresponding INSERT or UPDATE s, and that the size of the index is not a factor in the use of disk space.

Answer 2

You can think of an index as a way to pre-sort rows by values ​​in indexed columns. The index can be used to compare with >= and <= in the same way as c = .

Your option 3 is probably the best index in the sense that all WHERE clauses can be checked simply by looking at the index. Is this really the best index, depends on your dataset, because, for example, if most of your records have a very big value for end , all records will meet the WHERE end <= ? condition WHERE end <= ? , and the index will be of little use in this field (and the optimizer may decide not to use the index for this field because it will cause overhead without a "return on investment")

Answer 3

What you are looking for is the BETWEEN command, for this you do not need START and END .. you can do with only one row of the table.

 SELECT ID, REGION, START, END from COORD_SYSTEM WHERE REGION=? and TYPE=? BETWEEN 100 and 200;