Optimization of a mysql query with a subquery

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 :

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 :

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

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?