3

https://dev.mysql.com/doc/internals/en/optimizer-and-relations.html

The article says that if both the conditions are indexed and have equal join types, it uses the index that was created first.

In cases, where one index has higher cardinality, wouldn't it be better to chose that index as a driver. Shouldn't it query the storage engine?

If not should I force index in these cases, if I have an idea about the cardinality ?

Kshitij Banerjee
  • 1,678
  • 1
  • 19
  • 35
  • If I'm not mistaken, your question would fall into step 2 - MySQL would choose the index with better join type, would it not? If one index is better, it would be chosen over the other. Only if indexes have equal join type then the first one is chosen. I have never seen in the real world use that an index that's worse is chosen over a better one. However, interesting question, +1, let's see what others have to say. – N.B. Aug 20 '15 at 19:10
  • Join type refers to the mysql join type.. refer https://dev.mysql.com/doc/internals/en/optimizer-determining-join-type.html, as i understand it. – Kshitij Banerjee Aug 20 '15 at 19:30
  • I'm aware to what it refers, I have read the docs. Therefore, an index with higher cardinality has a better join type. Therefore, your question falls into step 2 of the mysql documentation explanation, **does it not**? – N.B. Aug 20 '15 at 19:31
  • example select * from table where key_1 > X and key_2 > Y, both are range join types.. regardless of cardinality. The cardinality does not affect join types. does it ? – Kshitij Banerjee Aug 20 '15 at 19:33
  • You can have a non-unique index yield an `eq_ref`, so I would *assume* that cardinality does affect the choice of a better index. – N.B. Aug 20 '15 at 19:49
  • Be careful of the term "join type"; it does not refer to `JOINing` two tables. – Rick James Aug 21 '15 at 05:23
  • @RickJames - I believe that both OP and I have stated that we are aware that "join type" does not refer to table joins. – N.B. Aug 21 '15 at 07:53

1 Answers1

1

I wonder if this documentation is simply out of date. After all, the description would seem to make analyze table much, much less useful. And that is documented as:

MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.

This description specifically contradicts the documentation in the internals document.

Or, perhaps this reference is poorly written and applies to situations where no other key distribution information is available. As you say, using the index with the higher cardinality would probably be a better place to start.

Also, as stated in the documentation itself, the use of "join type" is a poor description. This should really just be called "comparison type", or something like that.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Or, maybe it refers to the stored key distribution when figuring out actual table join orders and such, but for figuring out 'comparison_types' within a single tables conditions, it does not take them into consideration. – Kshitij Banerjee Aug 20 '15 at 19:52
  • Also, I hope this is out of date. :D – Kshitij Banerjee Aug 20 '15 at 19:54
  • 1
    I suspect the document cited is simply wrong. Using the index that was created first isn't a likely or plausible heuristic in the first place. – user207421 Aug 20 '15 at 22:20