1

I have a query which returns me the result after more than one minute:

select distinct o.OMS_IDTOPO,e.TYPEQPTL,e.LIBUPR, o.OMS_SITEA,o.OMS_SITEB, siteA.Code_Postal as CPA, siteB.Code_Postal as CPB, siteA.Nom_court as NomA, siteB.Nom_court as NomB, siteA.Commune as ComA,siteB.Commune as ComB, siteA.Lat_GPS as latA,siteA.Long_GPS as longA, siteB.Lat_GPS as latB,siteB.Long_GPS as longB from occ_wdm_oms as o left join noeud_dico_traitement as siteA on o.OMS_IDNOEUDA=siteA.Code_DICO left join noeud_dico_traitement as siteB on o.OMS_IDNOEUDB=siteB.Code_DICO left join occ_wdm_eqpt as e on o.OMS_IDTOPO=e.OMS_IDTOPO where o.OMS_IDTOPO NOT LIKE '%.VP%' and siteA.Lat_GPS != 0 and siteA.Long_GPS != 0 and siteB.Lat_GPS != 0 and siteB.Long_GPS != 0;

However, if I make a projection like

select distinct o.OMS_IDTOPO,e.TYPEQPTL,e.LIBUPR, o.OMS_SITEA,o.OMS_SITEB, siteA.Code_Postal as CPA, siteB.Code_Postal as CPB, siteA.Nom_court as NomA, siteB.Nom_court as NomB, siteA.Commune as ComA,siteB.Commune as ComB, siteA.Lat_GPS as latA,siteA.Long_GPS as longA, siteB.Lat_GPS as latB,siteB.Long_GPS as longB from occ_wdm_oms as o left join noeud_dico_traitement as siteA on o.OMS_IDNOEUDA=siteA.Code_DICO left join noeud_dico_traitement as siteB on o.OMS_IDNOEUDB=siteB.Code_DICO left join (select distinct LIBUPR,TYPEQPTL,OMS_IDTOPO from occ_wdm_eqpt) as e on o.OMS_IDTOPO=e.OMS_IDTOPO where o.OMS_IDTOPO NOT LIKE '%.VP%' and siteA.Lat_GPS != 0 and siteA.Long_GPS != 0 and siteB.Lat_GPS != 0 and siteB.Long_GPS != 0;

It takes just 10 seconds.

I've found two similar questions :Which MySQL JOIN query is more efficient? and Optimizing join in vertica.

The answers tell me that normally the first version is more efficient.

And I explain the two query, the result looks like tell me the same thing.

Explain for first query

Explain for another query

I'm confused because in reality I get the result more quickly by the query with projection, if there are something I've missed ?

Improve this question
8
  • Site A and Site B are INNER JOINs. Just sayin'! Commented Mar 19, 2018 at 16:32
  • Thanks for reply, it just make no difference Commented Mar 19, 2018 at 16:38
  • There's no derived table in the first query. I think you've got something muddled up. Commented Mar 19, 2018 at 16:38
  • The problem appears to be a lack of usable indexes on the first and fourth tables. Fix that, and your problems will miraculously disappear. Commented Mar 19, 2018 at 16:39
  • Do those queries return the same output? You have a distinct in the top of your query, so you are not able to see if both returns the same. Try to remove it and see if the first query returns much more records than the second one. The fields from occ_wdm_eqpt could be repeated several times in the first query, joining with all of them. However, in the second query you would be joining just with the distinct values Commented Mar 19, 2018 at 16:39
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.