Optimizing SQL queries is quite simple task, you just need to follow some rules on how you sort the filtering criteria and how you use your select statements. I will try to explain how you can optimize your queries in this post.
named columns
Use named columns, instead of using the * wildcard. For example:
select
t.*
from
tbl as t
where
t.tab_date > current_date - interval 1 month
order by
t.tab_date desc;
Will be more slower than:
select
t.tab_id,
t.tab_state,
t.tab_date,
t.tab_name
from
tbl as t
where
t.tab_date > current_date - interval 1 month
order by
t.tab_date desc
join filtering
If you filter on join, your query will be faster, for example:
select
t1.tab_id,
coalesce(t2.tab_date, current_date) as tab_date,
t1.tab_name
from
table1 as t1
left join table2 as t2 on (t1.tab_id = t2.tab_tab_id)
where
t2.tab_date IS NULL or t2.tab_date > current_date - interval 1 month;
Will be more slower than
select
t1.tab_id,
coalesce(t2.tab_date, current_date) as tab_date,
t1.tab_name
from
table1 as t1
left join table2 as t2 on (t1.tab_id = t2.tab_tab_id
and t2.tab_date > current_date - interval 1 month);
smaller set first
The smaller set must selected first, for example:
select
t1.tab_id,
coalesce(t2.tab_date, current_date) as tab_date,
t1.tab_name
from
table1 as t1
left join table2 as t2 on (t2.tab_tab_id = t1.tab_id
and t2.tab_date > current_date - interval 1 month);
Will be more slower than:
select
t1.tab_id,
coalesce(t2.tab_date, current_date) as tab_date,
t1.tab_name
from
table1 as t1
left join table2 as t2 on (t1.tab_id = t2.tab_tab_id
and t2.tab_date > current_date - interval 1 month);
Since t1 has less rows than t2, and t2 is referencing t1. Assuming that t1 has a smaller set of rows and is the referenced table. This applies to column selection too and where statements.
notes
You must measure the time of your queries, so you can have a more precise query. Those rules are not absolute.
Entries 