MySql Query Super Slow
I have the following query which is super slow (taking 4-5 seconds just for the results. I was wondering if anyone could see anything that I could do differently with this query to speed it up?
Thanks!
SELECT
accounts.id AS account_id,
accounts. NAME AS account_name,
accounts.assigned_user_id account_id_owner,
users.user_name AS assigned_user_name,
opportunities_cstm.firstname_c, opportunities_cstm.lastname_c,
opportunities.`name`, TRIM(
Concat(
Ifnull(
opportunities_cstm.firstname_c,
''
),
' ',
Ifnull(
opportunities_cstm.lastname_c,
''
)
)
) AS 'cfull' FROM
opportunities
LEFT JOIN users ON opportunities.assigned_user_id = users.id
LEFT JOIN accounts_opportunities ON opportunities.id = accounts_opportunities.opportunity_id
LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id
LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
WHERE
(
(
opportunities.sales_stage IN (
'Prospecting',
'Appointment Set',
'MeetAndGreet',
'Qualification',
'Needs Analysis',
'Locating Vehicle',
'Demo',
'Trade Evaluation',
'Negotiation',
'Manager T/O',
'Write Up',
'Credit App Submitted',
'Pending Finance',
'Loan Approval',
'Deposit',
'Delayed Decision',
'Sold-Vehicle Ordered',
'Sold-Pending Finance',
'Sold/Pending Delivery',
'Price Quoted',
'Service Pending'
)
)
)
AND (
accounts_opportunities.deleted IS NULL
OR accounts_opportunities.deleted = 0
)
AND (
accounts.deleted IS NULL
OR accounts.deleted = 0
)
AND opportunities.deleted = 0
ORDER BY
opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
Here is the explain from the same query:
╔═════════════╦════════════════════════╦════════╦══════════════════════════╦═════════════════════╦═════════╦════════════════════════════════════════════╦═══════╦═════════════════════════════╗ ║ select_type ║ table ║ type ║ possible_keys ║ key ║ key_len ║ ref ║ rows ║ extra ║ ╠═════════════╬════════════════════════╬════════╬══════════════════════════╬═════════════════════╬═════════╬════════════════════════════════════════════╬═══════╬═════════════════════════════╣ ║ simple ║ opportunities ║ range ║ sales_stage, idx_deleted ║ sales_stage ║ 78 ║ null ║ 25161 ║ Using where; Using filesort ║ ║ simple ║ users ║ eq_ref ║ PRIMARY, idx_id_deleted ║ PRIMARY ║ 108 ║ version4.opportunities.assigned_user_id ║ 1 ║ ║ ║ simple ║ accounts_opportunities ║ ref ║ idx_oppid_del_accid ║ idx_oppid_del_accid ║ 111 ║ version4.opportunities.id ║ 1 ║ Using where; Using index ║ ║ simple ║ accounts ║ eq_ref ║ PRIMARY,idx_accnt_id_del ║ PRIMARY ║ 108 ║ version4.accounts_opportunities.account_id ║ 1 ║ Using where ║ ║ simple ║ opportunities_cstm ║ eq_ref ║ PRIMARY ║ PRIMARY ║ 108 ║ version4.opportunities.id ║ 1 ║ ║ ╚═════════════╩════════════════════════╩════════╩══════════════════════════╩═════════════════════╩═════════╩════════════════════════════════════════════╩═══════╩═════════════════════════════╝
I see two problems.
First, you're using two different WHERE (... IS NULL OR ... = 0) criteria. Those are unspeakably slow. This is because indexes aren't useful for looking for NULL values. If you can get rid of the possibility of NULL in those deleted columns, perhaps by declaring them NOT NULL DEFAULT 0 you can change those criteria to WHERE ... = 0 . This should speed up lots of things. This is because indexes aren't useful for looking for NULL values.
Second, you're creating a great big joined result set, then sorting it to find the most recent items.
You might try preselecting the items from your 'opportunities' table before you do your join. Do something like this:
SELECT whatever....
FROM (
SELECT *
FROM opportunities
WHERE opportunities.deleted = 0
AND opportunities.sales_stage IN (
'Prospecting',
'Appointment Set', etc etc ...
'Service Pending' )
ORDER BY opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
) opportunities
LEFT JOIN users ON opportunities.assigned_user_id = users.id
...
ORDER BY
opportunities.date_entered DESC,
opportunities.id DESC
LIMIT 0,21
This very possibly could speed things up by reducing the cardinality of your LEFT JOIN operation by removing a bunch of records from the right-hand side of the join.
I am not sure it would make any improvement, but what I would try is this:
SELECT your_fields
FROM
(SELECT * --or just the fields you need
FROM
opportunities
WHERE
opportunities.deleted = 0 AND
opportunities.sales_stage IN (stage1, stage2, ...)
) opportunities1
LEFT JOIN users ON opportunities1.assigned_user_id = users.id
LEFT JOIN accounts_opportunities ON opportunities1.id = ...etc...
WHERE
(accounts_opportunities.deleted IS NULL
OR accounts_opportunities.deleted = 0)
AND (accounts.deleted IS NULL OR accounts.deleted = 0)
ORDER BY ...etc...
Let me know if it makes any improvement (but it could also be slower). Another idea is to use a table with all the stages you need to filter:
CREATE TABLE filter_stage (
stage varchar(255));
(255 or try to match the actual length of sales_stage, and it's better if you index this column also) where you enter all the strings to filter:
INSERT INTO filter_stage VALUES ('Prospecting'), ('Appointment Set'), ('...'), ...
then you remove your IN clause from your first query, and your FROM then becomes:
FROM
opportunities INNER JOIN filter_stage
ON opportunities.sales_stage = filter_stage.stage
LEFT JOIN ...
Let me know if it works!
Do not use IN. IN works slow in mysql, use Exists
DROP TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable ( sales_stage VARCHAR(50) NOT NULL );
insert into tempTable () values ('Prospecting'),('Appointment Set'),('MeetAndGreet'),...,('Service Pending');
SELECT
...
WHERE EXISTS(select sales_stage from tempTable where opportunities.sales_stage = sales_stage);
上一篇: 有大量行的MySQL性能问题
下一篇: MySql查询超慢