Databases
StackOverflow2013
Postsdbo
POUnable to FORCE INDEX on LEFT JOIN subquery?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnable to FORCE INDEX on LEFT JOIN subquery?
    primarykey
    data
    text
    <p>I have a complicated query which involves a subquery inside a LEFT JOIN statement. The process time is ~2s. If I replace the subquery with a temporary table, I hit the same ~2s. If I create an index in the temporary table, I have ~0.05s.</p> <p>I am trying to figure out why I can't work with Mysql USE INDEX or FORCE INDEX directly in the LEFT JOIN STATEMENT.</p> <p>Here is a simple query which will return a 'check your mysql syntax' error.</p> <pre><code>SELECT * FROM table1 t1 LEFT JOIN (SELECT id_project FROM table2) t2 FORCE INDEX FOR JOIN (id_project) ON (t2.id_project = t1.id_project); </code></pre> <p><strong>Why can't I use a FORCE INDEX with a LEFT JOIN subquery?</strong></p> <p>--edit: here is the actual query returning mysql error</p> <pre><code>SELECT p.id_projet, pa.piste_affaire, ty.type, p.e_force, gr.groupe, p.client, p.intitule_affaire, ag.agence, st.statut, p.winratio, p.justification_nogo, p.webdoc, sp.sponsor, dis.domain_is, cons.constructeur, p.date_chgt_statut, p.date_creation, p.ca_estimatif, p.tcv, p.marge, tarp.target_price, p.marketable_price, p.remise_propal_initiale, p.remise_propal_reel, p.date_de_signature, p.debut_build, p.fin_build, his.date, his.login, co.contrat, p.duree_contrat, p.fqp, cap.capitalisation, p.international, p.risk_management, p.asi, GROUP_CONCAT(com.commentaire,',') AS COMMENT FROM piste_affaire pa, statut st, TYPE ty, agence ag, domain_is dis, sponsor sp, constructeur cons, groupe gr, target_price tarp, contrat co, historique his, capitalisation cap, projet p LEFT JOIN commentaire com ON (p.id_projet=com.id_projet) LEFT JOIN bizdev bizd ON (p.id_bizdev = bizd.id_bizdev ) LEFT JOIN (SELECT eco_projet.id_projet, GROUP_CONCAT( CONCAT(ecosysteme) ORDER BY ecosysteme SEPARATOR ', ' ) AS ecosysteme FROM ecosysteme NATURAL JOIN eco_projet, projet WHERE eco_projet.id_projet = projet.id_projet GROUP BY eco_projet.id_projet) AS tmpeco /* if I delete that line, everything works as intended with ~2s query*/ FORCE INDEX FOR JOIN (id_projet) /* ** */ ON (tmpeco.id_projet = p.id_projet) WHERE ag.id_division != 2 AND dis.id_division != 2 AND st.id_division != 2 AND pa.id_division != 2 AND cons.id_division != 2 AND p.type_division = 1 AND pa.id_piste_affaire = p.id_piste_affaire AND p.id_statut = st.id_statut AND p.id_type = ty.id_type AND p.id_agence = ag.id_agence AND p.id_domain_is = dis.id_domain_is AND p.id_sponsor = sp.id_sponsor AND p.id_constructeur = cons.id_constructeur AND p.id_groupe = gr.id_groupe AND p.id_target_price = tarp.id_target_price AND p.id_contrat = co.id_contrat AND p.id_projet = his.id_projet AND p.id_capitalisation = cap.id_capitalisation GROUP BY p.id_projet; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSimon Peyou
    1. USSimon Peyou
    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
    1. This table or related slice is empty.
    1. COIs there any reason for the subquery or you just used it to check what's going to happen? I really see no need for it there.
      singulars
      1. POUnable to FORCE INDEX on LEFT JOIN subquery?
      1. USN.B.
    2. COIs this the "complicated" query? Or you do have some complex beast there? (please post it in that case).
      singulars
      1. POUnable to FORCE INDEX on LEFT JOIN subquery?
      1. USypercubeᵀᴹ
    3. COI used the example code as a simple example which will return a mysql error. Of course the actual code is way more complicated than that and the subquery is needed.
      singulars
      1. POUnable to FORCE INDEX on LEFT JOIN subquery?
      1. USSimon Peyou
 

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