[Solved] mySQL Select where results from column \"b\" have column \"a\" in common
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

mySQL Select where results from column \"b\" have column \"a\" in common

Database type:

This one is kinda hard to explain, I'll give it a shot.

I have this table where one of the columns is the type column. The salesperson will insert records that will contain a b_id and also an action_id.

with the following code I retrieve some info,

SELECT entry_type, COUNT(DISTINCT(b_name)) AS '# of prospects',
SUM(case when entries.out_id = '1' then 1 else 0 end) 'No Interest',
SUM(case when entries.out_id = '2' then 1 else 0 end) 'Needs Follow Up',
SUM(case when entries.out_id = '3' then 1 else 0 end) 'Appointment Booked'
FROM entries
LEFT JOIN outcomes on outcomes.out_id = entries.out_id
LEFT JOIN type on type.type_id = entries.type_id
LEFT JOIN business on entries.b_id = business.b_id
LEFT JOIN users on users.user_id = entries.user_id
WHERE b_name LIKE 'July%' AND (entries.type_id = 1 OR entries.type_id = 2 OR    entries.type_id = 14)
GROUP BY entry_type;

The result is the following

ACTION              # OF PROSPECTS  NO INTEREST  NEEDS FOLLOW UP  APP. BOOKED
Call                4               1            2                1
Follow Up Contact   2               0            0                2
Walk In             1               1            0                0

The thing is, There are 2 possible initial actions, "Call" or "Walk In". "Follow Up Contact" is used if necessary after a initial call or walk in. As you can see, I have 2 appointments booked originated from this follow up. Here is the question. How do I know if this follow up contact is related to an initial call or an initial walk in?

I need to be able to generate a report specifying how many appointments were originated from each type of approach ( call or walk in ).

Thanks in advance

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  2. Prefer IN Clause Over OR Conditions (modified query below): Using an IN clause is far more efficient than OR conditions, when comparing a column to more than one optional values. When using an IN clause, the database sorts the list of values and uses a quick binary search.
The optimized query:
SELECT
        entry_type,
        COUNT(DISTINCT (b_name)) AS '# of prospects',
        SUM(CASE 
            WHEN entries.out_id = '1' THEN 1 
            ELSE 0 END) 'No Interest',
SUM(CASE 
    WHEN entries.out_id = '2' THEN 1 
    ELSE 0 END) 'Needs Follow Up',
SUM(CASE 
    WHEN entries.out_id = '3' THEN 1 
    ELSE 0 END) 'Appointment Booked' 
FROM
entries 
LEFT JOIN
outcomes 
    ON outcomes.out_id = entries.out_id 
LEFT JOIN
type 
    ON type.type_id = entries.type_id 
LEFT JOIN
business 
    ON entries.b_id = business.b_id 
LEFT JOIN
users 
    ON users.user_id = entries.user_id 
WHERE
b_name LIKE 'July%' 
AND (
    entries.type_id IN (
        1, 2, 14
    )
) 
GROUP BY
entry_type 
ORDER BY
NULL

Related Articles



* original question posted on StackOverflow here.