CODEWITHSUNDEEP
mysqldatabase
D_R
D_R

Reputation: 4962

Making my mysql query more efficient

What I am trying to do is pulling the id,phone_type,os_version columns from Enswitch_Mobile_Users table.

And with the id i've just got to get the enswitch_id from Enswitch_Users table.

And after that to COUNT all the entires from Enswitch_Android_Purchases or Enswitch_Iphone_Purchases which the user colum match the id from enswitch_mobile_users. and getting first entry date and the last entry date.

And I managed to made it work with this query: 

SELECT p.user  AS `Mobile_User_ID`, 
    e.os_version `Os_Version`, 
    e.phone_type `Phone_Type`, 
    eu.enswitch_id `Enswitch_ID`,
    Count(1) AS `Buy_Count`,
    (SELECT pc.date 
        FROM 
        (
            SELECT date, user, status 
            FROM enswitch_android_purchases 
            UNION 
            SELECT date, user, status 
            FROM enswitch_iphone_purchases
        ) AS pc 
        WHERE pc.status = 1 
            AND pc.user = p.user 
        ORDER BY pc.date ASC 
        LIMIT 1) AS `First_Purchase`,
    (SELECT pc.date 
        FROM 
        (
            SELECT date, user, status 
            FROM enswitch_android_purchases 
            UNION 
            SELECT date, user, status 
            FROM enswitch_iphone_purchases
        ) AS pc 
        WHERE pc.status = 1 
            AND pc.user = p.user 
        ORDER BY pc.date DESC LIMIT 1) AS `Last_Purchase`
FROM 
(
    SELECT item, date, user, status 
    FROM enswitch_android_purchases 
    UNION
    SELECT item, date, user, status 
    FROM enswitch_iphone_purchases
) AS p 
LEFT JOIN enswitch_mobile_users e 
    ON p.user = e.id
LEFT JOIN enswitch_users eu 
    ON e.user_id = eu.id
WHERE p.`date` >= :from_date 
    AND p.`date` <= :to_date 
    AND p.user is not null 
    AND p.status = 1
GROUP BY `Mobile_User_ID`

But because of the selects it will be really slow so how can I make it more efficient?

Upvotes: 0

Views: 51

Answers (1)

Taryn
Taryn

Reputation: 247710

You might be able to use the following which replaces the two selects in the SELECT list with min(p.date) and max(p.date):

SELECT p.user  AS `Mobile_User_ID`, 
    e.os_version `Os_Version`, 
    e.phone_type `Phone_Type`, 
    eu.enswitch_id `Enswitch_ID`,
    Count(1) AS `Buy_Count`,
    min(p.date) AS `First_Purchase`,
    max(p.date) AS `Last_Purchase`
FROM 
(
    SELECT item, date, user, status 
    FROM enswitch_android_purchases 
    UNION
    SELECT item, date, user, status 
    FROM enswitch_iphone_purchases
) AS p 
LEFT JOIN enswitch_mobile_users e 
    ON p.user = e.id
LEFT JOIN enswitch_users eu 
    ON e.user_id = eu.id
WHERE p.`date` >= :from_date 
    AND p.`date` <= :to_date 
    AND p.user is not null 
    AND p.status = 1
GROUP BY p.user

Upvotes: 1

Related Questions