Timeline for Optimizing a slow MySQL query
Current License: CC BY-SA 3.0
4 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 17, 2014 at 16:26 | comment | added | ypercubeᵀᴹ |
If your queries have both conditions (WHERE SiteID = SomeConstant AND VariableID = AnotherConstant), then the order of the 2 columns inside the index does not matter. Either of the 2 suggested indexes will be good. Keeping the LocalDateTime in the last part of the index as the condition on that column is a range, not equality.
|
|
| Apr 17, 2014 at 14:37 | comment | added | cherner | Thanks for the detailed response. The index (SiteID, VariableID, LocalDateTime) did speed things up a lot. I'm wondering about the ordering of the index a bit more-- there are more SiteIDs than VariableIDs and there are more LocalDateTimes than SiteIDs and VariableIDs. So what I am trying to say is that SiteID narrows down the query the most, then VariableID, then LocalDateTime. Is (SiteID, VariableID, LocalDateTime) then the appropriate index? | |
| Apr 17, 2014 at 14:29 | vote | accept | cherner | ||
| Apr 16, 2014 at 21:14 | history | answered | ypercubeᵀᴹ | CC BY-SA 3.0 |