I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age.

We are using "vertical partionning": 1 table per criter. For example:

* user_country
- id_user
- id_country

We would like to do this kind of query:

SELECT id_inscri FROM userdata_langue
WHERE id_langue='43'
SELECT id_inscri FROM userdata_sexe
WHERE sexe='2'
SELECT id_inscri FROM userdata_nb_jour
WHERE nb_jour>='31'
SELECT id_inscri FROM userdata_last
WHERE last<='2013-04-07'
  AND last>='2013-04-03' ;

How do I optimize that?

----- More details

Explain output of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY userdata_langue ref id_langue   id_langue   1   const       398846  Using index
2   UNION   userdata_sexe   ref sexe    sexe    1   const   1667137 Using index
3   UNION   userdata_nb_jour    range   nb_jour nb_jour 2   NULL    5830    Using where; Using index
4   UNION   userdata_last   range   last    last    3   NULL    371614  Using where; Using index
NULL    UNION RESULT    <union1,2,3,4>  ALL NULL    NULL    NULL    NULL    NULL


Table   Create Table
userdata_langue CREATE TABLE `userdata_langue` (
 `id_inscri` bigint(20) NOT NULL,
 `id_langue` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY (`id_inscri`),
 KEY `id_langue` (`id_langue`)
  • 3
    Would converting those UNION to UNION ALL be an option? Are there duplicates that have to be removed? – ypercubeᵀᴹ Apr 23 '13 at 20:10
  • 1
    Do each one of the 4 selects run fast? Are there (and what) indexes in the tables? – ypercubeᵀᴹ Apr 23 '13 at 20:13
  • Each table has 2 index : - a PRIMARY key on the first column (id_inscri) - an INDEX on the second column 1 and 2 are running fast but return a lot of lines. 3 and 4 are a little more slow but return less lines. – François Apr 23 '13 at 20:17
  • 2
    A helpful bit of info would be this: how long do the individual queries take to run, and how long does the UNION ALL query take to run? – Michael - sqlbot Apr 26 '13 at 0:46
  • 1
    @François Are you sure about the query and what you want it to show? You say "selecting US people, men, more than 35 years of age." but the query will return all that are either speaking English(?) (id_langue='43') or are men (sexe='2') or have nb_jour>='31' or have last (updated/changed profile) between 3 and 7 of April 2013 (last<='2013-04-07' AND last>='2013-04-03'). Are you sure you want or and not and there? Either men or US or ... does not make much sense. – ypercubeᵀᴹ Jul 8 '13 at 5:58

From the EXPLAIN output, it looks like first table userdata_langue would benefit from an index that contains both id_langue and id_inscri. If there is already an index on that combination, try forcing it.

The second table userdata_sexe could be partitioned on sexe (I assume there are only two options here?). This would speed up the scan required to fetch all those.

I am not sure if mySQL would benefit from moving the order of the UNION DISTINCT components around. It might be worth trying to move component 3 and 4 up to the top of the query.

Finally, consider if you can afford to increase the sort buffer size in InnoDb (see: http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/) to make sure you can hold everything in memory while mySQL calculates the UNION DISTINCT.


Just have a try with below query

SELECT distinct id_inscri
FROM userdata_langue, userdata_sexe, userdata_nb_jour, userdata_last
WHERE (userdata_langue.id_inscri = userdata_sexe.id_inscri = userdata_nb_jour.id_inscri =  userdata_last.id_inscri) 
and ( (id_langue='43') or (sexe='2') or
(nb_jour>='31') or (last<='2013-04-07' AND last>='2013-04-03'))
  • 1
    The WHERE condition (the first, a=b=c=d part) does not do what you expect it to do (SQL is not Python!) and even if it did, the result would be the intersection, not the union of the subqueries the OP has. – ypercubeᵀᴹ Jul 8 '13 at 5:48
  • My bad, if you correct the python-style condition, it will return the union as the original (but only under the assumption that all the id_inscri values appear in all 4 tables - which is a valid assumption, since OP mentions vertical partitioning.) – ypercubeᵀᴹ Jul 8 '13 at 6:22
  • 2
    And then the distinct might be unnecessary. (It would definitely be so if it was certain that id_inscri values were unique in all the tables involved.) – Andriy M Jul 8 '13 at 6:34

Consider using UNION ALL. It should be significantly faster than UNION, but it will return duplicate rows for players that answers to multiple criteria.

If you can deal with duplicates in your application code, it can be a good way to optimize your query.

  • Unfortunately we really want unique ID :) – François Apr 23 '13 at 20:13
  • Even so, You can take your data using 'union all' keyword and make a filter by 'group by id_inscri' in the final resultset – Praveen Prasannan Apr 25 '13 at 11:38
  • 3
    You "could," though you're asking the server to do essentially the same thing as union distinct -- de-duplicating a set of ~1.7 million unindexed rows in a temporary table, so that isn't likely to be any faster. – Michael - sqlbot Apr 26 '13 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.