mysql - Why do two joins runs faster than one with an OR condition? -

I ask my database for a list of those places which are either the teacher's primary place or place Availability is provided for information.

If I meet two like this:

  SELECT tea. *, Avail_loc *, Pri_loc. * Leave the teacher in the form of tea (tea.teacher_id = ava.teacher_id and ava.end_date> + 13 9 8706428) as left places on avail_loc JOIN (ava.location_id = avail_loc.location_id) on left as pri_loc Places JOIN (tea.location_id = pri_loc.location_id) Where tea.active = 1  

My query takes .05 seconds. The problem is that I have to clear the output in php because my locations are split between avail_loc (optional location) and primary location (primary location).

Therefore, if I add them one to one or conditional, the query takes .8 seconds.

  SELECT tea. *, Loc. * Leave the teacher in the form of tea Ava on as available availability (Tea teacher_id = ava.teacher_id and ava.end_date> 1398706428) Places JOIN (ava.location_id = loc.location_id or tea as loc as left) .location_id = loc.location_id) Where tea.active = 1  

Interpret When I use the first query is indexed, which matches everything. When I run a second query, then it is missing my join or join.

Why do two more or more join one with OR? As a result the data is the same.

First of all, these two questions are not equal. If there is a match in each table, the first one will return the line and the other will return the two rows.

The answer to your question, however, is to adapt to a bad thing of optimizing MySQL under the or conditions as you note, it remembers this fact. It is possible that different indexes can be used to complete each sequence. Honestly, this is a problem with most database engines if you want the effect of or with better performance, then union all often works better:

  SELECT tea. *, Loc. * Teachers include availability in the form of tea (tea.teacher_id = ava.teacher_id and ava.end_date> + 13 9 8706428) Ava as places loc as the loc JOIN (tea.location_id = loc.location_id Where tea.active = 1 union all .. Select Tea *, loc teachers as Tea Left * Available as AVA JOIN (tea.teacher_id = ava.teacher_id and ava.end_date> +13 9 8706428) in the form of loc at left places (Ava .location_id = loc.location_id) Where is the tea Active = 1;