0

I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have placed orders/made purchases.

I'm running the below query but it's taking forever, is there something wrong with the query?

SELECT 
    COUNT(user.id)
FROM
    cx_user
        JOIN
    quiz_client ON quiz_client.user_id = user.id
        JOIN
    sales_order ON user.email = sales_order.customer_email
  • 1
    Try to index the column email in tables which you used to join. Best practice is to avoid direct join based on string try using email id and map it in the sales_order table. – James Oct 4 '19 at 7:49
  • @James thanks for the help, adding index on customer_email table has speedup the query to 5 seconds! – Iftikhar uddin Oct 4 '19 at 10:34
  • 1
    If you replace instead of email to it's id(int) it will be much faster and check whether the other column in join is indexed.. – James Oct 4 '19 at 10:37
  • 1
    All JOINed fields (exactly the) same datatype? And indexed as @James says? And JOIN on INTEGERs if possible (SMALLINT (32K - 64K UNSIGNED) or MEDIUMINT even better). – Vérace Oct 4 '19 at 20:36
  • Please provide EXPLAIN SELECT ... – Rick James Oct 13 '19 at 1:41
1

Change to

COUNT(DISTINCT user.id)

Else you are counting "purchases", not "users".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.