0

Building on this answer: https://stackoverflow.com/a/2349824/1382306

Are there further guidelines in A and B's usages, or are they equally important?

I have a SELECT FROM table1, table2 WHERE table2.table1id = table1.id AND table2.constant = ?.

In my case, I know that ? is from a user-defined variable. Does that make a difference since it never changes?

Could I push my luck and ask for an explanation of the logic used against (A,B) and (B), like "all Bs are found that have As, and all Bs are searched next".

Many thanks in advance!

Community
  • 1
  • 1

2 Answers2

2

You haven't written any question in your question but I assume you want to know what kind of indexes you should use.

On table2 you should have an index on (constant, table1id) and on table1 you have your primary key id anyway. If you only select a few columns from table2 you should include them in the index as well. This way MySQL can answer your query right from the index, without even loading the data of table2. It does that by looking up constant in the index, which it can do because constant is the leftmost column of the index and then loads table1id also from the index. If that happens you can see Using index in the output from EXPLAIN.

AndreKR
  • 32,613
  • 18
  • 106
  • 168
1

Hidden in another popular post https://stackoverflow.com/a/4120191/1382306, it looks like

"Queries need to address the order of columns when dealing with a covering index (an index on more than one column), from left to right in index column definition. The column order in the statement doesn't matter, only that of columns 1, 2 and 3 - a statement needs have a reference to column 1 before the index can be used. If there's only a reference to column 2 or 3, the covering index for 1/2/3 could not be used.

In MySQL, only one index can be used per SELECT/statement in the query (subqueries/etc are seen as a separate statement). And there's a limit to the amount of space per table that MySQL allows. Additionally, running a function on an indexed column renders the index useless"

There's a good list of index pitfalls in that answer too.

In my case since "only one index can be used per SELECT/statement in the query" I'm guessing that using only (?, table2.table1id) is the way to go.

Community
  • 1
  • 1