Some of my database search queries are taking too long. Is there a way to improve its performance?
Hi. Can you post what queries are slow?
I upgraded my hosting plan and it seems to have improved as far as the query speed. Most of them took less and 1.5 sec. I found a couple that took longer. See below:
# Mon Feb 8 15:05:17 2016
# Thread_id: 684639 Schema: xxx_liveperson Last_errno: 0 Killed: 0
# Query_time: 1.924218 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 Rows_read: 1
# Bytes_sent: 52
use xxx_liveperson;
SET timestamp=1454969117;
UPDATE livehelp_users SET cookieid='7ba998a5b51db0da3e7fdf88bc70b735',identity='208.184.161-
cslhVISITOR',ipaddress='208.184.161.172',cookied='Y',new_session='N',lastaction='20160208220515',expires='20160208222515'
WHERE sessionid='785c0a545d1655d5e3b70261d0e05a0d'
# Sun Feb 7 21:20:52 2016
# Thread_id: 247482 Schema: xxx_abantecart Last_errno: 0 Killed: 0
# Query_time: 14.013470 Lock_time: 0.000076 Rows_sent: 16 Rows_examined: 2152 Rows_affected: 0 Rows_read: 2152
# Bytes_sent: 452
use xxx_abantecart;
SET timestamp=1454905252;
SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT ptc.product_id
FROM products_to_categories ptc
WHERE ptc.category_id IN (64, 307))