Databases
StackOverflow2013
Postsdbo
POUsing an index in MySQL JOIN with OR condition
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing an index in MySQL JOIN with OR condition
    primarykey
    data
    text
    <p>I'm running a query which looks like this</p> <pre><code>SELECT parent.field, child.field FROM parent JOIN child ON (child.id = parent.id OR child.id = parent.otherid) </code></pre> <p>This is however really slow (about 100k records, and JOINs to other tables in the real version), but despite having tried indexes on </p> <pre><code>parent.id (PRIMARY), parent.otherid, child.id (PRIMARY), and a composite index of parent.id and parent.otherid </code></pre> <p>I cannot get MySQL to use any of those indexes when making this join.</p> <p>I read that MySQL can use only one index per join, but can't find anywhere whether it can use a composite index when a JOIN contains an OR condition.</p> <p>Does anyone here know if it's possible to make this query reference an index? If so, how?</p> <hr> <p>MY SOLUTION</p> <p>(SO won't let me answer my own question below atm)</p> <p>A bunch of tweaking and came up with a fairly decent solution which retains the ability to JOIN and aggregate other tables.</p> <pre><code>SELECT parent.field, child.field FROM parent JOIN ( SELECT parent.id as parentid, # Prevents the need to union IF(NOT ISNULL(parent.otherid) AND parent.otherid &lt;&gt; parent.id, parent.otherid, parent.id) as getdataforid FROM parent WHERE (condition) ) as foundrecords ON foundrecords.parentid = parent.id JOIN child ON child.id = parent.getdataforid </code></pre> <p>For speed requires a condition inside the subquery to reduce the number of records placed in a temporary table, but I have tons of additional joins on the outer query, some joining to the child and some to the parent (with some aggregates) so this one worked best for me.</p> <p>In many cases a union will be faster and more effective, but since I'm filtering on parent, but want additional data from child (parent self-references), the union caused extra rows for me which I couldn't consolidate. It's possible the same result can be found just by joining parent to itself and aliasing a where condition in the outer query, but this one works quite nicely for me.</p> <p>Thanks to Jirka for the UNION ALL suggestion, it's what prompted me to get here :)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBob Davies
    1. USBob Davies
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing an index in MySQL JOIN with OR condition
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing an index in MySQL JOIN with OR condition
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing an index in MySQL JOIN with OR condition
      1. USmgraphic
      1. VTFavorite
    1. CODid you run `explain select ...` on the query?
      singulars
      1. POUsing an index in MySQL JOIN with OR condition
      1. USjuergen d
    2. COyep, the explain is what's letting me know it's not using any indexes for this join.
      singulars
      1. POUsing an index in MySQL JOIN with OR condition
      1. USBob Davies
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload