SOS. select from many tables which has the same structure

[mysqlfan]

In order to shorten the content of a database table, I divide it into 12 tables by month. Therefore, every table has the same structure. I name those tables with different names, like t_04_2004, t_05_2004 ...

Now I want to select a distinct value of a column from those 12 tables, is it possible to do it by sql statement? otherwise, i have to select the distinct value of a column from each table, push them into an array and popup the min one which sounds not wise..

tia

 

[swampBoogie]

Use one table only. With proper indexing, it should not be any problem performancewise.

(To combine data from different tables you can use a union query.)

 

[mysqlfan]

tx4 reply !

i seperate the table into 12 months a yr because it would become to too huge if i do not divide it.. because i want each hotel manager save their hotel rates pro day and 365 days pro yr. which means, if i got 10k hotels use the system, it will be 3,65 M rows. it will slow down the system very much as i can imagine. it might have more than 100k hotels joining, who knows..

will u pls teach me how to use mysql statement to select distinct rate from those 12 - 24 tables which have the same structure.

thanks a ton !

 

[jbeninger]

SwampBoogie was right. If you index the tables properly, the size won't matter. If you're doing queries like

hotel_date BETWEEN "2001-01-04" AND "2001-01-08"

, it should actually be faster since it won't have to search the whole month.

But if you've already got the code down, or are hell-bent on using the twelve-month method anyway, UNIONS are the way to go.

SELECT col1, col2 FROM january
UNION
SELECT col1, col2 FROM february
UNION
SELECT col1, col2 FROM march
...