GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimization of a mysql query with a subquery

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimization of a mysql query with a subquery

    Hi !


    I need your advice to see if it is possible to optimize this request:
    Code:
    SELECT * FROM advert ads INNER JOIN advert_text txt on ads.advertId = txt.adstAdvertId WHERE txt.adstLang = 'nl' OR (txt.adstLang = ads.advertMainLang AND NOT EXISTS(SELECT NULL FROM advert_text WHERE adstLang = 'nl' AND adstAdvertId = ads.advertId));
    In explain I have this as result for the subquery :
    # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
    '2', 'DEPENDENT SUBQUERY', 'advert_text', 'eq_ref', 'ADST_LANGandADVERITD,ADSID_idx,ADSTLANG', 'ADST_LANGandADVERITD', '10', 'ads.advertId,const', '1', 'Using where; Using index'

    I dont know if this request is better or not:
    Code:
    SELECT * FROM advert ads INNER JOIN advert_text txt on ads.advertId = txt.adstAdvertId WHERE txt.adstLang = 'nl' OR (txt.adstLang = ads.advertMainLang AND ads.advertId NOT IN (SELECT adstAdvertId FROM advert_text WHERE adstLang = 'nl'));
    In explain I have this as result for the subquery :
    # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
    '2', 'DEPENDENT SUBQUERY', 'advert_text', 'unique_subquery', 'ADST_LANGandADVERITD,ADSID_idx,ADSTLANG', 'ADST_LANGandADVERITD', '10', 'func,const', '1', 'Using index; Using where'
    The result that I want is :
    1. If the description exist in the language of the visitor (in this case "nl"), I want that MySQL return that description (with the rest of the first table).
    2. If the description don't exist in the language of the visitor, I want that MySQL return the description that correspond to the language of the author (ads.advertMainLang).
    Maybe it is better to retrieve everything (in visitor language and original language) and treat after the result directly in PHP
    Code:
    SELECT * FROM advert ads INNER JOIN advert_text txt on ads.advertId = txt.adstAdvertId WHERE txt.adstLang = 'nl' OR txt.adstLang = ads.advertMainLang;

    If you see a better way to do this, please tell me.

    Thank you

  • #2
    These are always difficult to correct without being able to see results, but try remove that "if not exists" - that will always be problematic and make the query grow slower as the database increases.
    Maybe you can rather do a UNION between the first part of the WHERE and then move the "OR" part to a union with a left join?

    Comment

    Working...
    X