Query Optimization with MySQL

Cristiano D. Silva
Mar 1 · 8 min read

Query Construction

SELECT
-- columns
--- sub-selects
--- functions
--- improper aggregations
-- main table
--- joins
--- sub-selects
-- where clauses-- aggregation
--- groupping, ordering, having and limiting
SELECT f.id, f.name, f.description, f.type_id
FROM foo f
JOIN foo_type ft ON ft.id = f.type_id
WHERE ft.id = 2;
SELECT f.id, f.name, f.description, f.type_id
FROM foo f
WHERE f.type_id = 2;
select 
distinct o.id as order_id
, round(sum(oi.amount * oi.quantity), 2) as total
from orders o
join order_items oi on oi.order_id = o.id
join order_type ot on ot.id = o.order_type_id
where o.create_date between '2019-01-01 00:00:00' and '2019-01-01 23:59:59'
and oi.type_id in (1,2,3)
and o.type_id = 2
group by oi.id
select 
distinct o.id as order_id
, round(sum(if o.type_id != 2, 0, oi.amount * oi.quantity), 2) as total
from orders o
join order_items oi on oi.order_id = o.id
join order_type ot on ot.id = o.order_type_id
where o.create_date between '2019-01-01 00:00:00' and '2019-01-01 23:59:59'
and oi.type_id in (1,2,3)
group by oi.id
select 
distinct o.id as order_id
, round(sum(if o.type_id != 2, 0, oi.amount * oi.quantity), 2) as total
from orders o
join order_items oi on oi.order_id = o.id
where o.create_date between '2019-01-01 00:00:00' and '2019-01-01 23:59:59'
and oi.type_id in (1,2,3)
group by oi.id
having sum(oi.amount * oi.quantity) > 999.00

Saved by Explain

explain extended select 
distinct o.id as order_id
, round(sum(if o.type_id != 2, 0, oi.amount * oi.quantity), 2) as total
from orders o
join order_items oi on oi.order_id = o.id
where o.create_date between '2019-01-01 00:00:00' and '2019-01-01 23:59:59'
and oi.type_id in (1,2,3)
group by oi.id

All options exhausted

Resources

If this post was helpful, please click the clap 👏 button below a few times to show your support for the author! ⬇

Faun

The Must-Read Publication for Aspiring Developers & DevOps Enthusiasts

Cristiano D. Silva

Written by

#PhP Software Engineer, entrepreneur on #spa and #rejuvenation business (#bodybrowbar) and #photographer enthusiast.

Faun

Faun

The Must-Read Publication for Aspiring Developers & DevOps Enthusiasts