Optimization of a mysql query with a subquery

[MDi]

Hi !

I need your advice to see if it is possible to optimize this request:

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:

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 : [LIST=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).
[]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).
[/LIST]
Maybe it is better to retrieve everything (in visitor language and original language) and treat after the result directly in PHP

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

[alaincraven]

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?