Optimize SQL query

Question

I am trying to optimize this slow query (> 2s)

SELECT COUNT(*) FROM crmentity c, mdcalls_trans_activity_update mtu, mdcalls_trans mt WHERE (mtu.dept = 'GUN' OR mtu.dept = 'gun') AND mtu.trans_code = mt.trans_code AND mt.activityid = c.crmid AND MONTH(mtu.ts) = 2 AND YEAR(mtu.ts) = YEAR(NOW()) AND c.deleted = 0 AND c.smownerid = 28 

This is the result when I use EXPLAIN:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c index_merge PRIMARY,crmentity_smownerid_idx,crmentity_deleted_smownerid_idx,crmentity_smownerid_deleted_idx crmentity_smownerid_idx,crmentity_deleted_smownerid_idx 4,8 NULL 91 Using intersect(crmentity_smownerid_idx,crmentity_deleted_smownerid_idx); Using where; Using index 1 SIMPLE mt ref activityid activityid 4 pharex.c.crmid 60 1 SIMPLE mtu ref dept_idx dept_idx 5 const 1530 Using where 

It uses the index I created (dept_idx), but it takes more than 2 seconds to complete a query on a data set of 1380,384 records. Is there any other way to express this query in an optimal way?

UPDATE Using David's suggestions, the query is now reduced to a few milliseconds instead of working for more than 2 seconds (in fact, 51 seconds in MySQL version 5.0).

Answer 1

What is the most selective part of the WHERE ? That is, which condition removes the most potential elements from the result set?

I would suggest that this is a mtu.ts filter. If so, you should also index the mtu.ts column and try to restrict it so that the index can be used; for example, using the BETWEEN statement.

Other tips:

• Join join clauses directly to the connection using JOIN ... ON () , this makes it easy to read the query for both people and the optimizer
• Avoid calculating constants in a query, for example YEAR(NOW())
• Avoid the functions of the selected columns in the WHERE clause, such as MONTH(mtu.ts) . This reduces the opportunities for mass use of indexes.
• Normalize data to avoid casing problems, for example mtu.dept = 'GUN' OR mtu.dept = 'gun' ; one UPDATE mtu SET dept = lower(dept) and the corresponding CHECK dept = lower(dept) on the table will help to avoid such madness.

Answer 2

• I would rewrite the request using connections. This is more understandable and gives the optimizer better chances.
• MONTH (mtu.ts) = 2 AND YEAR (mtu.ts) = YEAR (NOW ()) - it is better to use mtu.ts between .. and ..

Answer 3

Could you change the text string to a number?

Answer 4

The most obvious solution I see is to change COUNT (*) to cover only one field name, otherwise your index may be next to useless!

Answer 5

As a general principle, a good approach to analyzing such problems is to understand the data that you agree on, to evaluate its power.

In other words, order your request so that the most selective things happen first. What is more likely in your data that dept = 'GUN' or that userId will be 28.

Lasty, do you think you are joining MT and MTU instead of filtering? This can make your query much faster, since you will limit the amount of data that requires date matching.