Getting Data From Many Different MySQL Tables

Question

I have 6 tables with exactly the same structure, the field names go like this:

country | incoming | outgoing_national | outgoing_us | outgoing_uk | outgoing_global | outgoing_text

I'm trying to code a rates table where the rates change instantly (jusing jQuery) when a country is selected from a dropdown. Because this is going to be happening as soon as they choose a country it needs to be as fast as possible.

So basically for example where country = 'England' I need the data from all the different tables so I can display it all on the page. I've been playing about with a code I've got from a previous similar question but I'm getting confused trying to work it out. I'd appreciate a helping hand of any sort :D

Answer 1

SELECT * FROM Table_1 WHERE country='England'
UNION 
SELECT * FROM Table_2 WHERE country='England'
.
.
.

and so on.

If you want to see rows that are identical in two or more tables use UNION ALL

Answer 2

The fastest way to do this is via union all. Of course, that doesn't remove duplicates, so if you need to do that, then use union instead (though you'll take a performance hit). Moreover, you'll want to index the country column in all of the tables. Doing this will pull back all of those queries very quickly.

Secondarily, why do you have six tables of all the same structure? This seems a bit out of place to me.

To get all of the countries available:

select distinct
    country
from table1

To get all of the data within a country:

select
    *
from
    table1 t1
where
    country = 'England'
union all
select
    *
from
    table2 t2
where
    country = 'England'

...

union all
select
    *
from
    table6 t6
where
    country = 'England'