Reputation: 3928
indexing question
I have a table w/ 3 columns: name, phone, date. I have 3 indexes: 1 on phone, 1 on date and 1 on phone and date. I have the following statement:
SELECT * FROM ( SELECT * FROM people WHERE phone IS NOT NULL ORDER BY date DESC) as t GROUP BY phone
Basically, I want to get all unique phone numbers ordered by date. This table has about 2.5 million rows but takes forever to execute....are my indexes right?
UPDATE:
My EXPLAIN statement comes back with 2 rows: 1 for primary table and 1 for derived table.
It says I am using temporary and using filesort for my primary table.
For my derived table, it says my possible keys are (phone), and (phone, date) but it is using filesort.
Upvotes: 0
Views: 99
Answers (4)
Reputation: 1
I think the problem may be the "is not null" clause. Using this clause causes the db to miss the index. It has to do a full table scan to check the condition. Consider using a different default value to represent null so you can hit your index.
Upvotes: 0
Reputation: 16673
you should not need the extra select * from
SELECT distinct phone
FROM people
WHERE phone IS NOT NULL
ORDER BY phone, date DESC
UPDATE - for all th ecolumns try this:
SELECT name, date, distinct phone
FROM people
WHERE phone IS NOT NULL
ORDER BY phone, date DESC
Upvotes: 1
Reputation: 8292
I think your 3rd index is redundant, since it should already be covered by the individual indexes on the date and phone columns.
However, in your case I don't think the indexes are the real cause of the query being slow. Instead the real problem is probably the inner query that is producing huge temporary data set and as far as I know MySql is not really optimized for this.
UPDATE:
I think the following query should have the same output as yours, but would avoid the inner select:
SELECT phone, max(date) as maxDate
FROM people
WHERE phone IS NOT NULL
GROUP BY phone
ORDER BY maxDate DESC
Upvotes: 1
Reputation: 15989
If you have an index (phone, date) an index of (phone) is not needed as MySQL can easily use the first index for that.
Depending on the amount of NULL phonenumbers the indexes are irrelevant. You first ask the database server to get all elemenmts with phone number, if the majority has a phone number it won't care about the index then sort all of that by date, than sort again by phone and aggregate, so assuming most of your entries have a phone number you are sorting two times.
You can easily write this in a single query:
SELECT * FROM people WHERE phone IS NOT NULL GROUP BY phone, date DESC
See also the output of
EXPLAIN SELECT * FROM ( SELECT * FROM people WHERE phone IS NOT NULL ORDER BY date DESC) as t GROUP BY phone
vs.
EXPLAIN SELECT * FROM people WHERE phone IS NOT NULL GROUP BY phone, date DESC
Upvotes: 0