CODEWITHSUNDEEP
mysqlsqlunion
Cryssie
Cryssie

Reputation: 3175

Query from multiple mySQL table at the same time

I want to query some data, but the data is distributed into different tables based on the month (e.g.201301, 201302) the data was collected. I can try to query the tables by doing a union from all the tables I need. This is what I have so far..

select * from table_2013_01
where user_id = 39858
UNION ALL
select * from table_2013_02
where user_id = 39858
UNION ALL
.
.
.
select * from table_2014_01
where user_id= 39858
order by date asc

My questions are:

  1. Is there a better way than this as I might want to retrieve data from 2010 year onwards? That would mean doing a union of all 36 tables in one go?
  2. Will this cause performance issue as this is a live server and I cannot afford to do query for more than 2 minutes or it will send an alert to the DBA.

NOTE: I only have read access from the DB. I cannot even create a temp table.

Upvotes: 0

Views: 76

Answers (2)

Siddharth Kumar
Siddharth Kumar

Reputation: 1715

If you can create/put index on user_id (it seems you don't have any other parameter in where clause), then the retrieval will be faster, and then you can do

    select * from (
      select * from table_2013_01
      where user_id = 39858
      UNION ALL
      select * from table_2013_02
      where user_id = 39858
      UNION ALL
      .
      .
      .
      select * from table_2014_01
      where user_id= 39858
    ) t order by date asc;

If you have access to create procedures, then you need not to manually write these query, you can create dynamic SQL (String say *final_sql*) and can run it:

PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

7alhashmi
7alhashmi

Reputation: 924

Try something like this:

select *
from (select * from table_2013_01 
  union all 
  select * from table_2013_02 
  union all 
  select * from table_2013_03) a
where a.user_id = 12212;

Upvotes: 0

Related Questions