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?
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.