How to join mysql tables with similar structure

[SandraJose]

Hi

I have multiple tables, tb_1, tb_2, tb_3. Each having the similar structure like id, title, content, and ratings. So as for now I am using following structure.

(Select*from tb_1 order by rating DESC) union (select*from tb_2 order by rating DESC) union (select*from tb_3 order by rating DESC) to list all titles from 1,2,3 tables, but the problem is, it only lists tables one after another. I want to list top ten rated titles from all the tables, so let me know how can I do that?

[Rozanne]

Select * From (Select* from tb_1 union all select* from tb_2 union all select* from tb_3) order by rating DESC limit 10

However, you need to re-think about your current table design, because lot of tables with the similar base name, but with a different incrementing suffix that all have the similar structure surly looks like an unskilled design.

[SandraJose]

Okay I will try to implement your recommendation. But currently I have more than 700,000 items arranged into various tables alphabetically. It might be too big to fill up all of them in a single table and I am concerned that query would be too slow, so what do you think about this problem?

[Kristine Habeck]

I think you should use a single table, and study about indexing. That be helpful for you.

[SandraJose]

@Kristine Habeck

Alright, I will check that part as well, but I am still worried about my large database.

[Rozanne]

If you are database is not able to cope up 700,000 rows in a one table, then it's not desirable to called it as a database. FYI, mysql is able to handle a lot more than that.

[SandraJose]

But I think it will make queries slower than usual.

[Rozanne]

If you do proper indexing, then it won't affect on query speed. The query we are talking about, including all the table will most probably even be faster. You shouldn't exist your database design, because you are thinking it could be slower. So make your design perfect, then run your queries. After this check the slow ones and try to set up them.

[Kristine Habeck]

If you cannot set up them for a particular query that meets your requirement, then you might think about switching to a good design to a crappy design.

[Henry Rose]

Which version of mysql you are using? If it's a 5.1, then you can use table partitioning with the indexes for even better performance. You should consider manual for more information.

[Rozanne]

No........OP just need to cope up 700,000 rows, so there is no need of partitioning.