mysql 5.5 compound index order

Question

I got this table(made up but similar )

Items (ot_id,numval1,numval2)

And I do a query like this:

Select from OtherTable ot JOIN Items on ot.id=Items.ot_id where 
Items.numval1 > 1 and Items.numval2 < 300

Now when i create the compound index on Items to cover above query can I do:

create index  idx1 on Items (ot_id,numval1,numval2);

Is the order correct ? Do I need the ot_id in there ?

Answer 1

Case 1 (This is not your case, but bear with me)

FROM Items
    where  ot_id = 321
      and  numval1 > 1
      and  numval2 < 300 

Have two indexes:

INDEX(ot_id, numval1)
INDEX(ot_id, numval2)

od_id is first because it is tested with =. Once a range (eg, numval1 > 1) is hit, no further columns in the index can be used.

The Optimizer will pick between them based on which range test (numval1 or numval2) might be faster. The Optimizer cannot use the 3-column index you suggested. (Or, rather, it will not use the third column.)

You have not said what values you will be SELECTing. If it is no more than those 3 columns, then a "covering" index will be a slight benefit:

INDEX(ot_id, numval1, numval2)
INDEX(ot_id, numval2, numval1)

Again, this leaves it to the Optimizer to dynamically decide which is better.

As Akina points out, the Optimizer could screw up and fail to notice that a table scan would be better.

Case 2 (The query as you have it)

     from  OtherTable ot
     JOIN  Items  ON ot.id=Items.ot_id
    where  Items.numval1 > 1
      and  Items.numval2 < 300 

Notice that the WHERE clause talks only about Items. So, it is extremely likely that the Optimizer will start with it, then JOIN to OtherTable. Again it leads to 2 choices:

INDEX(numval1)
INDEX(numval2)

Or, if covering:

INDEX(numval1, numval2, ot_id)
INDEX(numval2, numval1, ot_id)

(The last pair of columns can be in either order.)

Then, to get to ot, there needs to be an index on id, presumably, you have PRIMARY KEY(id)?

(So, I disagree with Akina's answers.)