Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Cool Unanswered: How to join tables with same structure

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fopgames
    What do you suggest?
    you should use one table only, and learn about indexing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    Thanks Rudy, I'll consider that.
    Last edited by fopgames; 07-25-09 at 13:56.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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

  7. #7
    Join Date
    Jul 2009
    Posts
    4
    But won't that make queries slower?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  9. #9
    Join Date
    May 2009
    Posts
    2
    In 5.1 you can use table partitioning with the indexes for even better performance. See the manual for more detail.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by BCB
    In 5.1 you can use table partitioning with the indexes for even better performance. See the manual for more detail.
    For only 500.000 rows no partitioning should be needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •