mysqlquery-optimizationunionsql-optimization

How to optimize mysql query with union of two tables having more than 750,000 records in both tables?

I have two tables propeties_1 and properties_2. Table propeties_1 has 350,000 records and Table propeties_2 has 400,000 records.

I am using query as following:

Select union_table.* FROM
(
    (select col1 as c1, col2 as c2, col3 as c3 from `propeties_1` where status='A')
    union
    (select colm1 as c1, colm2 as c2, colm3 as c3 from `propeties_2` where status='A')
) as union_table 
limit 12 offset 0 order by c1;

This query takes too much time in execution.

How can I optimize this query?

Solution

  • You can greatly optimize your query if you have propeties_1.status and propeties_2.status marked as INDEX on database.

    You can easily create it with the following instructions:

    CREATE UNIQUE INDEX index_status1 on propeties_1(status);
CREATE UNIQUE INDEX index_status2 on propeties_2(status);

    Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.