MySQL Selection of data from several tables with the same structure, but with different data.

Question

Well, here is my dilemma. I have a database consisting of 5 tables with the same data structure. Data is shared in this way for localization purposes and breaks a total of about 4.5 million records.

In most cases, only one table is required, and all is well. However, sometimes data is needed from two or more tables, and they need to be sorted by a user-defined column. Here I am having problems.

data columns:

id, band_name, song_name, album_name, genre 

MySQL Statute:

 SELECT * from us_music, de_music where `genre` = 'punk' 

MySQL spits out this error:

 #1052 - Column 'genre' in where clause is ambiguous 

Obviously, I am doing it wrong. Does anyone want to shed light on this for me?

Answer 1

I think you are looking for UNION , a la

 (SELECT * from us_music where `genre` = 'punk') UNION (SELECT * from de_music where `genre` = 'punk') 

Answer 2

It looks like you will be sitting with one table. Five of them have the same layout and sometimes must be presented as if they came from the same table in order to put all this in one table.

Add a new column that can be used to distinguish between the five languages ​​(I assume that it is a language that is different from tables, because you said that it is intended for localization). Do not worry about having 4.5 million records. Any real database can handle this size without any problems. Add the correct indexes and you will not have problems with them, as with a single table.

Answer 3

Any of the above answers is valid or an alternative way is to expand the table name to include the database name, for example:

 SELECT * from us_music, de_music where `us_music.genre` = 'punk' AND `de_music.genre` = 'punk' 

Answer 4

The column is ambiguous because it appears in both tables, and you need to specify a field where (or sort), for example us_music.genre or de_music.genre, but you usually specify two tables if you are going to join them together in some way. The structure you encounter is sometimes referred to as a partitioned table, although this is usually done to split the dataset into separate files, rather than simply dividing the dataset arbitrarily. If you are responsible for the structure of the database, and there is no reason to separate the data, then I would build one large table with an additional field "origin" containing the country code, but you probably do this because of legitimate performance, Or use a join, to join the tables of interest to you http://dev.mysql.com/doc/refman/5.0/en/union.html or using the database merge mechanism http://dev.mysql.com/doc/refman/5.1/ en / merge-storage-engine.html .

Answer 5

Your initial attempt to span both tables creates an implicit JOIN. This is unhappy with most experienced SQL programmers, as it separates tables that must be combined with the condition of how.

UNION is a good solution for tables as they are, but there should be no reason why they cannot be placed in the same table with decent indexing. I saw adding the correct index to a large table to increase the query speed by three orders of magnitude.

Answer 6

The union statement causes transaction time in huge data. It is good to make a choice in 2 stages:

• select id
• then select the main table with it