How to join tables with same structure

fopgames · #1 (**permalink**) 07-25-09, 12:17

I have multiple tables, table_a, table_b, table_c. Each having the same structure. i.e ID, title, rating, content. I'm using

(select * from table_a order by rating DESC) union (select * from table_b order by rating DESC) union (select * from table_c order by rating DESC) to list all titles from a,b,c tables. But it only lists tables one after the other.

How can I list top ten rated titles from all the tables.

shammat · #2 (**permalink**) 07-25-09, 12:31

Code:

SELECT *
FROM 
( 
  SELECT * 
  FROM table_a
  UNION ALL
  SELECT * 
  FROM table_b
  UNION ALL
  SELECT * 
  FROM table_c
) t
ORDER BY rating DESC
LIMIT 10

But you should really re-think your table design.
A lot of tables with the same base name, but with a different incrementing suffix that all have the same structure sure sounds like bad design.
Your current problem is just one proof that this is not right

fopgames · #3 (**permalink**) 07-25-09, 12:40

Thank you very much, I'll try out your suggestion. I have more than 500,000 items arranged into different tables alphabetically. It might be too heavy to fill all of 'em in a single table. And even the query would be too slow. What do you suggest?

r937 · #4 (**permalink**) 07-25-09, 12:42

Quote:

Originally Posted by fopgames

What do you suggest?

you should use one table only, and learn about indexing

fopgames · #5 (**permalink**) 07-25-09, 12:53

Thanks Rudy, I'll consider that.

shammat · #6 (**permalink**) 07-25-09, 13:10

Quote:

Originally Posted by fopgames

I have more than 500,000 items arranged into different tables alphabetically. It might be too heavy to fill all of 'em in a single table. And even the query would be too slow.

No. A database that cannot handle 500,000 rows in a single table is not worth to be called a database.
MySQL can handle a lot more than that.
500,000 rows can't even be called a "big" table

fopgames · #7 (**permalink**) 07-25-09, 13:33

But won't that make queries slower?

shammat · #8 (**permalink**) 07-25-09, 13:44

Quote:

Originally Posted by fopgames

But won't that make queries slower?

Not with proper indexing.

The query we are talking about (that includes all tables) will most probably even be faster.

You should not jeopardize your DB design because you think it could be slower.

First get your design correct, then run your queries, then check the slow ones and try to tune them.

If you can't tune a specific query so that the performance meets your needs then - and only then - you might think about moving from a good design to a bad design.