Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post Unanswered: query design (2 tables - same structure)

    hello

    here are the facts:
    - 2 tables: `table1` and `table2` that have the SAME structure
    - `table1` about 30.000 rows
    - `table2` about 170.000 rows - this table is updated more often
    - i have to display the data in a grid with pagination with results from both `table1` and `table2`

    i've did the following:
    CREATE TEMPORARY TABLE `table_tmp` LIKE `table1`;
    ALTER TABLE `table_tmp` ENGINE=MERGE UNION=(`table1`,`table2`);
    ANALYZE TABLE `table_tmp`;

    SELECT SQL_CALC_FOUND_ROWS
    [...]
    FROM `table_tmp`
    WHERE
    [...]
    LIMIT 0, 20


    is this the best method to work with the tables?
    i find it kind of slow. please share your thoughts.
    sometime i get this kind of error: Table 'table_tmp' is marked as crashed and should be repaired
    thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Thumbs down re

    actually with UNION ALL the query is much slower

    with temporary table MERGE i get the following results:

    CREATE TEMPORARY TABLE 0.001532
    ALTER TABLE UNION 0.002059
    mysql_query = 2.613590
    mysql_num_rows 0.000009
    FOUND_ROWS 0.000086
    mysql_result 0.000019
    mysql_free_result 0.000008
    119496 rows returned


    I dont like the fact that the mysql_query has the execution time of 2.6 seconds

Posting Permissions

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