CODEWITHSUNDEEP
mysqlquery-optimizationunionsql-optimization
Harpal Singh
Harpal Singh

Reputation: 702

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?

Upvotes: 0

Views: 372

Answers (2)

Rick James
Rick James

Reputation: 142298

It's a simple trick -- add ORDER BY and LIMIT to the inner queries. More discussion here, including what to do with OFFSET:

http://mysql.rjweb.org/doc.php/pagination#pagination_and_union

And have these composite indexes:

propeties_1:  INDEX(status, col1)
propeties_2:  INDEX(status, colm1)

Upvotes: 0

John
John

Reputation: 770

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.

Upvotes: 1

Related Questions