How can I streamline this SQL query?
I have received a warning from my shared hosting company to say I have a slow SQL query which is using resources and I need to fix it. I have been running this query for a long time without issue so perhaps the database is getting a lot bigger as time goes on, making this query slower. I do need it to do what it is doing but perhaps there is a better way that what I am doing here. Any suggestions would be much appreciated.
SELECT `prod_name`,
`start_date`,
`end_date`,
`reg_price`,
`sale_price`,
bbp.`prod_id`,
`sp_name`,
`order_id`,
`img_name`,
`outlets`,
po.`outlet_id`,
`product_slug`,
`long_desc`,
`short_desc`,
`website_url`,
`facebook_url`,
`twitter_url`,
`instagram_url`,
`contact_number`,
`contact_email`,
COUNT(voucher_id) AS totalVouchers
, SUM(CASE WHEN purchased = '0000-00-00' THEN 1 ELSE 0 END) AS TotalWithDate0000
FROM `vouchers` as `v`
INNER JOIN `bb_products` as `bbp` ON bbp.`prod_id` = v.`product_id`
INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
INNER JOIN `prod_pics` as `pp` ON pp.`prod_id` = bbp.`prod_id`
INNER JOIN (SELECT `product_id`, `outlet_id`, COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`
WHERE bbp.`prod_id` = :prod_id
AND pp.`featured` = 'yes'ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I dont see any issue with query, except this part that I have problem to understand...
other than that, as long as you have proper indexes on tables, you should be ok...
vouchers(product_id)
bb_products(prod_id)
service_providers(service_provider_id)
prod_pics(prod_id)
prod_outlets(product_id, outlet_id)
+ this select query looks weird to me...
Count(voucher_id) AS totalVouchers,other than that, as long as you have proper indexes on tables, you should be ok...
vouchers(product_id)
bb_products(prod_id)
service_providers(service_provider_id)
prod_pics(prod_id)
prod_outlets(product_id, outlet_id)
+ this select query looks weird to me...
INNER JOIN (SELECT product_id,
outlet_id,
Count(DISTINCT outlet_id) AS outlets
FROM prod_outlets
GROUP BY product_id) AS po INNER JOIN (SELECT product_id,
Count(DISTINCT outlet_id) AS outlets
FROM prod_outlets
GROUP BY product_id) AS po 
Bear in mind, my initial suggestion to run an EXPLAIN on your query should provide a lot of the information that we've all asked for, primarily as to whether or not an index is used and is effective.
ASKER
Thank you all, I have added EXPLAIN to my query but I don't know how/where to see the output?
I can' run the query in phpmyadmin because of all the dynamic variables I have so I don't quite know how to see the output. I am also assuming that I should be doing this:
I can' run the query in phpmyadmin because of all the dynamic variables I have so I don't quite know how to see the output. I am also assuming that I should be doing this:
EXPLAIN SELECT `prod_name`, .......
Hi,
You should be able to run the EXPLAIN on the query you posted in phpadmin and see the output.
https://code.tutsplus.com/tutorials/profiling-mysql-queries-with-phpmyadmin--net-12687
What do you mean by dynamic variables? Are you saying that your query can have more where clauses, other than shown in your post, that are dynamically created using selected values in a webpage form? If so, you need to execute explain on all possible combinations of the query and/or with all possible where clauses to see where your bottleneck is. Starting with the query as it was reported from your hosting company.
Regards,
Tomas Helgi
You should be able to run the EXPLAIN on the query you posted in phpadmin and see the output.
https://code.tutsplus.com/tutorials/profiling-mysql-queries-with-phpmyadmin--net-12687
What do you mean by dynamic variables? Are you saying that your query can have more where clauses, other than shown in your post, that are dynamically created using selected values in a webpage form? If so, you need to execute explain on all possible combinations of the query and/or with all possible where clauses to see where your bottleneck is. Starting with the query as it was reported from your hosting company.
Regards,
Tomas Helgi
If you're referring to parameter binding, I only see one - prod_id.
As Tomas said, did your company provide you with a sample query? If so, that should be the one you use EXPLAIN on.
As Tomas said, did your company provide you with a sample query? If so, that should be the one you use EXPLAIN on.
ASKER
@gr8gonzo, sorry, you are correct. So, I did it in phpmyadmin and not sure if this is what you are looking for?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that. Regarding the indexes, are you saying that I should in phpmyadmin add index to the column like I have done here as per the screenshot? I added an index to prod_id in the prod_pics table as an example.
As a start, yes, although Tomas provided some queries that would add some multi-column indexes. MySQL only uses the best possible index on each table for the given query. So while an index on prod_id is good, it's even better if you have one index that covers the prod_id AND featured columns.
I noticed one more thing - after adding the indexes, you could also add a WHERE clause into your prod_outlets table that re-uses your prod_id binding:
...FROM `prod_outlets` WHERE `prod_outlets`.product_id = :prod_id ...
That would reduce the number of rows returned in that sub-select before the join is applied.
...FROM `prod_outlets` WHERE `prod_outlets`.product_id = :prod_id ...
That would reduce the number of rows returned in that sub-select before the join is applied.
ASKER
I am unable to tell if the query itself is actually running faster now or not but the page itself seems to load the data faster which is a good sign. I will check with the web host and see if they are happy now. If not I will open the question again :)
Thanks to all for your help, it is much appreciated.
Thanks to all for your help, it is much appreciated.
Can you run EXPLAIN again on the same query after all the changes have been made?
ASKER
@gr8gonzo, I will be out of action for a few days but as soon as back I will give it a go and post the results.
2. The pp.featured field seems like it's a boolean field yet you're comparing it to the string "yes" - if it's truly a yes or no field, you might consider using a BIT field instead.
3. Your subquery on INNER JOIN could potentially make the query slower as data grows. If so, the question is how much. At a certain point, it can be worth it to perform the query separately and use code or a stored procedure to handle the cross-table interactions.