MySQL Forums
Forum List  »  Optimizer & Parser

Re: Another question about index's
Posted by: Marek Podmaka
Date: March 12, 2008 09:55AM

This index could not be used: date (data_Id, date)

Think your query is this:
SELECT r.date, rr.rop, value
FROM relation r JOIN cells rr ON (r.data_Id=rr.data_Id)
WHERE r.date='2008-1-4' AND rr.rop>=1 AND rr.rop<=96

I have separated the join condition from where (for mysql the syntax is equivalent). You are searching for specific date in the R table, so should have index on date. So mysql gets all matching records from R table using index, fetching the data_id field and finds corresponding rows in RR (using its primary key data_id). You can speed-up this query by having index on (date,data_id), so when mysql finds the row with date, it doesn'thave to read the data_id from table - it has it in the index. This is useful when you don't need any other fields from this table.

Options: ReplyQuote


Subject
Views
Written By
Posted
3172
January 05, 2008 06:35PM
Re: Another question about index's
2196
March 12, 2008 09:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.