CODEWITHSUNDEEP
mysql
stepanian
stepanian

Reputation: 11433

Slow MySQL distinct with where

I have a large table that has two columns (among others):

This query is very fast:

select distinct event-date from my_table

This query is also very fast:

select * from my_table where country = 'US'

However, this query is very slow:

select distinct event_date from my_table where country = 'US'

I tried adding all combinations of indexes, including one on both columns. Nothing makes the third query faster.

Any insights?

Upvotes: 1

Views: 45

Answers (2)

Alex
Alex

Reputation: 17289

ALTER TABLE my_table ADD INDEX my_idx (event_date, country);

Upvotes: 0

e_i_pi
e_i_pi

Reputation: 4820

Have you tried staging the results in a temporary table, adding an index, then completing the query from there? Not sure if this will work in MySQL, but it's a trick I use successfully in MSSQL quite often.

CREATE TEMPORARY TABLE IF NOT EXISTS staging AS (
    SELECT event_date FROM my_table WHERE country = 'US'
);
CREATE INDEX ix_date ON staging(event_date);
SELECT DISTINCT event_date FROM staging;

Upvotes: 1

Related Questions