# Optimization of a mysql query with a subquery

**URL:** <https://forums.percona.com/t/optimization-of-a-mysql-query-with-a-subquery/3643>\
**Category:** Other MySQL® Questions\
**Created:** [July 28, 2014, 3:25pm UTC](https://forums.percona.com/t/optimization-of-a-mysql-query-with-a-subquery/3643 "2014-07-28T15:25:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![MDi](https://avatars.discourse-cdn.com/v4/letter/m/5daacb/32.png) [@MDi](https://forums.percona.com/u/MDi)\
**Post date:** [July 28, 2014, 3:25pm UTC](https://forums.percona.com/t/optimization-of-a-mysql-query-with-a-subquery/3643/1 "2014-07-28T15:25:29Z")

</div>

Hi !

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

```auto
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:

```auto
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

```auto
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

---

<div class="post-metadata">

**Author:** ![alaincraven](https://avatars.discourse-cdn.com/v4/letter/a/edb3f5/32.png) [@alaincraven](https://forums.percona.com/u/alaincraven)\
**Post date:** [July 29, 2014, 12:06am UTC](https://forums.percona.com/t/optimization-of-a-mysql-query-with-a-subquery/3643/2 "2014-07-29T00:06:43Z")

</div>

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?