optimize the query

The following query takes a long time to execute because the table has lots ( Millions of records) of data so i would like to optimize the query so the execution will be faster and i will get results faster.

when i execute the following query then it hangs or takes long time to execute and quit.

Re: optimize the query

first, you can try to run the queries separately instead of union and see which one is taking more time. Also, you can try to break the 1st query into simple queries and create temporary tables and use the tables to join with other tables.

Re: optimize the query

I tried to seperate queries. First query runs too slow and timeouts when I have few millions of rows. other queries are fine. Can you suggest any changes in first query to make it faster? I already have indexes, still takes 5500 sec to run.

Re: optimize the query

In most of the above joins the tables are related via the .id field. In the first query m is joined to r only on the inequality on the date expression which will force a full product join comparing every row of m to every row of r. If either or both of those are any size, it will take a while.