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

Question

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?

@RiggsFolly, I need 12 records at a time but, results should be from union of tables — Harpal Singh, Mar 05 '20 at 11:22
@HarpalSingh There are multiple factors that play under optimization. I recommend going through this link for better understanding. https://stackoverflow.com/questions/60015018/laravel-use-mysql-indexing/60015685#60015685 — Digvijay, Mar 16 '20 at 10:56

score 1 · Accepted Answer · answered Mar 05 '20 at 11:29

1

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.

answered Mar 05 '20 at 11:29

John

770
1
9
18

1

Have you tried using [`UNION ALL`](https://www.tutorialspoint.com/sql/sql-unions-clause.htm) Clause? – John Mar 05 '20 at 11:37
I doubt seriously if `status` is `UNIQUE`?? – Rick James Mar 10 '20 at 03:00

score 0 · Answer 2 · answered Mar 10 '20 at 02:58

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)

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

2 Answers2