Do UNION Queries Run in Parallel in MySQL?

google-cloud-sqlmulti-threadMySQLparallelism

I have three tables and I want to select data from these three tables in parallel form to increase the performance.

I'm running a query like this.

SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3

I want to ask that these queries run in parallel form or in order one by one?

What can I do to increase the performance of this query? Can I create a thread for each query?

If I create three thread for this query with a single connection is it work in parallel or not? or I need to create three connection for each query to run parallel?

I read some article about this and I found that it depends on the processor core.
If there are multiple cores it automatically run in parallel form if it single
the processor it must be run one after another.

I test it in my computer Core i5. I think core i5 has at least two physical processor, but it seems it's not run in parallel form.

I found this when I run query with explain form two tables

id       select_type       table         partitions       type        possible_keys      key       key_len    ref      rows      filtered     Extra
------------------------------------------------------------------------------------------------------------------------------------------------------
1        PRIMARY          table1           NULL           ALL            NULL            NULL       NULL      NULL    122882      100.00       NULL
2        UNION            table2           NULL           ALL            NULL            NULL       NULL      NULL    122882      100.00       NULL
NULL     UNION RESULT   <union1,2>         NULL           ALL            NULL            NULL       NULL      NULL    NULL        NULL         Using temporary

Actually, I want to work on Google Cloud SQL is its support to run UNION queries in
parallel form or not.

Update 10/20/2017

TABLE1, TABLE2 and TABLE3

 id            int(11) Primary Key 
 title         varchar(100)
 description   varchar(250)
 pic           varchar(100)
 tag_id        int(11)
 list_id       int(11)
 video_src     varchar(100)

Every table contains more than 122882 rows. It's current position but it's
increasing very fast and going into millions. I don't want to face any problem in future.

It not exact but it tables something like this.
Reason of Partitions identical columns in different tables is that I want to save data according to the specific region. I think It's faster to get data from table if I know the user region. But some time I need to get data from more than one regions e.g from 3 tables. Actually there is not a only 3 table but a lot of tables PARTITION by country_province_city_tablename. But in example suppose
there is only 3 tables.

QUERIES

When I need to get data from specific region.

  SELECT * FROM `table1` WHERE id = 7

When I need to get data from multiple regions.

  SELECT * FROM `table1` WHERE id IN(1,5,7,3)
  UNION ALL
  SELECT * FROM `table2` WHERE id IN(1,6,3,8,2)
  UNION ALL
  SELECT * FROM `table3` WHERE id IN(6,54,1,5,7)

As you can see WHERE clause is only apply on PRIMARY KEY which is indexed by default. Which make it easy to search a row.

What you think PARTITION BY REGION is good or not ?
And How can I run these queries in Parallel form to increase performance ?

table1, table2 and table3 is just placeholder Replace these with exact names 

  table1 -->  PK_PNJ_PTK_Posts
  table2 -->  PK_ISB_RWP_Posts
  table3 -->  PK_PNJ_KSR_Posts

Rows in tables

    //table1 or PK_PNJ_PTK_Posts

    id      title     description    tag_id     list_id      video_src
   ----------------------------------------------------------------------
    1      Title1     desc1           37           13        path/to/file1
    2      Title2     desc2           43           34        path/to/file2
    3      Title3     desc3           433           4        path/to/file3
    4      Title4     desc4           53           36        path/to/file4
    5      Title5     desc5           43           31        path/to/file5
    6      Title6     desc6           73           54        path/to/file6
    7      Title7     desc7           3             9        path/to/file7
    8      Title8     desc8           53           56        path/to/file8
    9      Title9     desc9           13           32        path/to/file9
    ..     ....        ...            ..           ..          ....
    ..     ....        ...            ..           ..          ....


    //table2 or PK_ISB_RWP_Posts

    id      title     description    tag_id     list_id      video_src
   ----------------------------------------------------------------------
    1      Title1     desc1           37           13        path/to/file1
    2      Title2     desc2           43           34        path/to/file2
    3      Title3     desc3           433           4        path/to/file3
    4      Title4     desc4           53           36        path/to/file4
    5      Title5     desc5           43           31        path/to/file5
    6      Title6     desc6           73           54        path/to/file6
    7      Title7     desc7           3             9        path/to/file7
    8      Title8     desc8           53           56        path/to/file8
    9      Title9     desc9           13           32        path/to/file9
    ..     ....        ...            ..           ..          ....
    ..     ....        ...            ..           ..          ....


    //table3 or PK_PNJ_KSR_Posts

    id      title     description    tag_id     list_id      video_src
   ----------------------------------------------------------------------
    1      Title1     desc1           37           13        path/to/file1
    2      Title2     desc2           43           34        path/to/file2
    3      Title3     desc3           433           4        path/to/file3
    4      Title4     desc4           53           36        path/to/file4
    5      Title5     desc5           43           31        path/to/file5
    6      Title6     desc6           73           54        path/to/file6
    7      Title7     desc7           3             9        path/to/file7
    8      Title8     desc8           53           56        path/to/file8
    9      Title9     desc9           13           32        path/to/file9
    ..     ....        ...            ..           ..          ....
    ..     ....        ...            ..           ..          ....

Best Answer

MySQL does not use multiple CPUs for any query, not even with UNION or PARTITION (the likely candidates).

Multiple connections (not just multiple threads) could do things in parallel. But the overhead of making the connection and gathering the 3 sets of data may be worse.

To make things worse, UNION always creates a tmp table to gather the separate SELECTs into. (This inefficiency is being removed in 8.0 for many, not all, UNIONs.)

UNION ALL is more efficient than UNION DISTINCT or UNION, since there is no need for de-dupping.

Your 3 tables have very similar columns? If they are identical, I have to ask you why they are 3 different tables instead of a single table.

One inefficiency that you may be falling into is * -- If that includes large TEXT or BLOB columns that you don't need to fetch, then performance could be significantly hampered. (This is due to InnoDB's way of storing bulky stuff off-record.)

Parallelism is not always beneficial -- If there is too much contention for I/O, for example, there may be no benefit.

What will you do with the resultset? How many rows are you expecting. Give us more details; there may be some out-of-the-box solution that simply avoids this query.

Single table; 'id=7' can occur in multiple regions

First, I need to understand the semantics of id. Since I see 7 in multiple tables, I assume it came from some place other than table1/2/3? And 7 may exist in any number of the "regions"?

CREATE TABLE `All` (
     region ...,
     id ...,   -- Is this AUTO_INCREMENT?
     ...
     PRIMARY KEY(region, id), -- in this order; for 2nd query
     INDEX(id)   -- to keep AUTO_INCREMENT happy
     );

SELECT * FROM `All` WHERE region = 1 AND id = 7;
# Uses the PRIMARY key to find the row(s)
# Drawback:  secondary index lookup is slower than Primary.

SELECT * FROM `All` WHERE region = 1 AND id IN(1,5,7,3)
UNION ALL
...
# `UNION ALL` is better than `OR` in this context.

Single table; 'id=7' can occur in only one region

CREATE TABLE `All` (
     region ...,
     id ...,
     ...
     PRIMARY KEY(id),   
     # (optional) INDEX(region, ...)
     );

SELECT * FROM `All` WHERE id = 7;

SELECT * FROM `All` WHERE id IN(1,5,7,3, 1,6,3,8,2, 6,54,1,5,7)
# UNION and `region` are unnecessary and would slow it down

PARTITION BY LIST(region) and 'id=7' can occur in multiple regions

CREATE TABLE `All` (
     region ...,
     id ...,
     ...
     PRIMARY KEY(id, region)  -- in this order
     )
     PARTITION BY LIST(region) (
         PARTITION r1 (1),   -- (I may have the syntax wrong)
         PARTITION r2 (2),
         PARTITION r3 (3) );

SELECT * FROM `All` WHERE region = 1 AND id = 7;
# Assumes that you somehow know that '7' is in region '1'.
# Does do partition pruning, but that is no better than having the PK

SELECT * FROM `All` WHERE region = 1  -- partition pruning
                      AND id IN(1,5,7,3)
UNION ALL  -- still needed
...

Caution: Performance suffers when a table has more than about 50 PARTITIONs. (To be fixed in 8.0.)

Bottom line (assuming multiple 7s): Single un-partitioned table is about the same performance as partitioned table.

PS: In all cases (so far), UNION ALL is faster than OR (I believe).

Best

The best solution (though not very practical) is to renumber the records to be unique across all regions. Then have a single table, with queries that involve only id:

CREATE TABLE `All` (
     region ...,
     id ... AUTO_INCREMENT,
     ...
     PRIMARY KEY(id),   
     # (optional) INDEX(region, ...)
     );

SELECT * FROM `All` WHERE id = 17;

SELECT * FROM `All` WHERE id IN(11,15,17,13, 21,26,23,28,22, 36,354,31,35,37)
# UNION and `region` are unnecessary and would slow it down
Related Question