My query isn't running when adding a where clause to get all records after a certain date-CodePudding

So when the where clause isn't there the query works fine, but when I add it the query doesn't pull any results and just runs.

Basically what I'm trying to do is get all the customers who completed a survey on a website after 2021-11-17. One table has all the survey information whereas the other table has all the customer details which I need. customer_survey table has the survey details (reported_at date, survey_source (where they did the survey), customer_id) and the other table is Customers (email, phone number, last name, first name, id)

The where clause I'm trying to integrate is:

where survey_source = 'online' and reported_at > '2021-11-17'

This is the query that currently works:

SELECT customer_surveys.customer_id, customer_surveys.reported_at, customer_surveys.survey_source,customers.id,customers.last_name,customers.first_name,customers.home_phone, customers.work_phone,customers.email_address
FROM customer_surveys
JOIN customers on customer_surveys.customer_id = customers.id

CodePudding user response：

If I understand correctly, this is the actual query which is hanging:

SELECT
    c.id,
    c.first_name,
    c.last_name,
    c.home_phone,
    c.work_phone,
    c.email_address,
    c.reported_at,
    c.survey_source
FROM customers c
INNER FROM customer_surveys cs
    ON cs.customer_id = c.id
WHERE
    cs.survey_source = 'online' AND
    cs.reported_at > '2021-11-17';

One standard way to speed up the performance of this query would be to add one or more indices (indices = plural of index). For example, we can try adding the following index on the surveys table:

CREATE INDEX idx_survey ON customer_surveys (customer_id, survey_source, reported_at);

This index, if used, might help speed up the join condition.

CodePudding user response：

When you run this query in SSMS, check what is the execution plan that is generated. Check if it suggests creating any indices. If there are huge no of records and the query is performing bad then you need to create indices on the fields that are involved in filtering the data viz customer_id, survey_source, reported_at.