JLoe.net

Recently I was given a chance to get my mind and fingers into optimizing some heavy SQL queries on MySQL server. These examples are taken from real queries. I must admit that I am not an expert on this SQL stuff but I would like to share my useful findings in optimizing these SQL queries. Details of the queries, mainly table names, have been removed for clarity and security reasons. So check them out.

SELECT pig.*, pdt.*, ph.*
FROM pig, pdt, ph
WHERE
	LOWER( pdt.Status ) = 'active' AND
	pig.pid = pdt.pid AND
	pdt.pid = ph.pid AND
	pdt.SearchFriendlyUrl = 'xxx-xxx-xxx'

Showing rows 0 – 5 (6 total, Query took 4.5421 sec)

SELECT pig.*, pdt.*, ph.*
FROM
	(
		SELECT *
		FROM pdt
		WHERE
			SearchFriendlyUrl = 'xxx-xxx-xxx' AND
			LOWER(Status) = 'active'
	) pdt
	INNER JOIN pig ON pig.pid = pdt.pid
	INNER JOIN ph ON ph.pid = pdt.pid

Showing rows 0 – 5 (6 total, Query took 0.0107 sec)

Here my optimization strategy was to reduce the number of rows returned by the joined tables as much as possible and then to join the tables using INNER JOIN clause instead of WHERE clause.

SELECT
	pdt.*,
	dtd.name as ProductType,
	IFNULL
	(
		(
			SELECT GROUP_CONCAT( dtd.name SEPARATOR ' ' )
			FROM pt
				INNER JOIN dtd on pt.tid = dtd.tid
				INNER JOIN dv on dv.vid = dtd.vid AND LOWER( dv.name ) = 'travel type'
			WHERE
				pt.pid = pdt.pid AND
				LOWER( dtd.name ) != 'classic'
			GROUP BY pt.pid
			LIMIT 1
		),
		'Travel'
	) TravelType,
	(
		SELECT country.Name countryName
		FROM pt
			INNER JOIN country ON country.TId = pt.TId
		WHERE pt.PId = pdt.PId and country.vid = 6
		ORDER BY field( countryName, 'Multi-Country' ) DESC
		LIMIT 1
	) ProductCountry
FROM pdt, ptx, dtd, dv
WHERE
	pdt.pid IN
	(
		SELECT pId
		FROM pdr
		WHERE
			pdr.pId = pdr.pId AND
			pdr.parent = '156'
	) AND
	ptx.pid = pdt.pId AND
	dtd.tid = ptx.tid AND
	LOWER( pdt.Status ) = 'active' AND
	dtd.vid = dv.vid AND
	LOWER( dv.name ) = 'product type';

Showing rows 0 – 1 (2 total, Query took 3.0657 sec)

SELECT
	pdt . * ,
	dtd.name AS ProductType,
	IFNULL
	(
		(
			SELECT GROUP_CONCAT( dtd.name SEPARATOR ' ' )
			FROM pt
				INNER JOIN dtd ON pt.tid = dtd.tid
				INNER JOIN dv ON dv.vid = dtd.vid
			WHERE
				pt.pid = pdt.pid AND
				LOWER( dtd.name ) != 'classic' AND
				LOWER( dv.name ) = 'travel type'
			GROUP BY pt.pid
			LIMIT 1
		),
		'Travel'
	) TravelType,
	(
		SELECT country.Name countryName
		FROM pt
			INNER JOIN country ON country.TId = pt.TId
		WHERE
			pt.PId = pdt.PId AND
			country.vid =6
		ORDER BY field( countryName, 'Multi-Country' ) DESC
		LIMIT 1
	) ProductCountry
FROM pdr
	INNER JOIN pdt ON pdr.pid = pdt.pid
	INNER JOIN ptx ON pdt.pid = ptx.pid
	INNER JOIN dtd ON ptx.tid = dtd.tid
	INNER JOIN dv ON dtd.vid = dv.vid
WHERE
	pdr.parent = '156' AND
	LOWER( pdt.Status ) = 'active' AND
	LOWER( dv.name ) = 'product type'
ORDER BY pId

Showing rows 0 – 1 (2 total, Query took 0.0047 sec)

If you look carefully at the query above, I have removed the use of IN clause from the previous query. This is proven to have improved the performance by 99.8% from 3.0657 sec to 0.0047 sec. Well it’s clear that the improvement gained was remarkably significant.

SELECT *
FROM
	(
		SELECT *
		FROM pd
		WHERE LOWER( status ) = 'active'
	) pd
	INNER JOIN
	(
		SELECT *
		FROM pe
		WHERE parent = '857'
	) pe ON pd.pid = pe.pId

Showing rows 0 – 1 (2 total, Query took 0.1062 sec)

SELECT *
FROM pd
	INNER JOIN pe ON pd.pid = pe.pId
WHERE
	pe.parent = '857' AND
	LOWER( pd.Status ) = 'active'

Showing rows 0 – 1 (2 total, Query took 0.0008 sec)

Again, looking at the numbers the improvement once again was quite significant. This is achieved by using WHERE clause in the main query instead of in each of the joined tables. This is apparently opposite to my discussion on the first example.

Well, I won’t go into detail on why and how these figures come from as I don’t think I have the capability to explain the logic behind the numbers. I will probably leave the assumption to you now. I hope these examples may help you in producing more efficient SQL queries during your development. Have fun!

If you find this post helpful, appreciate if you could leave your rating below to indicate that this post is really useful and at the same time let others know about this post. Thank you friend :)

(No Ratings Yet)

 Loading ...

Don't leave just yet! You may also be interested to take a quick look at my other posts.

1. Zend Caveats
2. Web Performance Best Practices