Hi!
>>>>> "kr" == iso-8859-1 <iso-8859-1> writes:
kr> Hello & thanks for the hint,
kr> but unfortunately this seems not to be the current issue, for all SeriesIDs
kr> refer to about the same amount of rows (700-900). I also investigated some
kr> more with EXPLAIN, but now I need somebody, who could explain explain :),
kr> the manual and I couldn't do it...
kr> Mysql EXPLAINing the slow query:
kr> table type possible_keys key key_len ref rows Extra
kr> V0 range idx idx NULL NULL 823 ; Using index
kr> V1 ref idx idx 12 ???,???,V0.Time 685 Using index
kr> V2 ref idx idx 12 ???,???,V0.Time 685 Using index
kr> V3 range idx idx NULL NULL 682 ; Using index
kr> Mysql EXPLAINing the fast query:
kr> table type possible_keys key key_len ref rows Extra
kr> V3 range idx idx NULL NULL 682 ; Using index
kr> V0 ref idx idx 12 ???,???,V3.Time 685 Using index
kr> V2 ref idx idx 12 ???,???,V0.Time 685 Using index
kr> V1 ref idx idx 12 ???,???,V0.Time 685 Using index
The big difference is the order of the tables. It seams that for your
query, its better to have V3 first! You can force this to be first by
using STRAIGHT_JOIN or LEFT JOIN.
kr> The manual says, that key_len is NULL if key is NULL. But in the slow
kr> example, key is not NULL. One other thing, i didn't get -- how can I make
kr> "ref" to "eq_ref", which is supposed to be faster? Actually, the join takes
kr> only one row from each table ("as"-ed of the same table). Could this be
kr> done, with eq_ref somehow?
eq_ref means that MySQL is using a whole unique key to join the
tables. As you don't join on unique keys, this isn't possible.
kr> I also tried to enlarge all kinds of buffers (key_, join_) but this didn't
kr> help.
<cut>
>> My query - for drawing 4 lines on a chart:
>>
>> SELECT V0.Val, V1.Val, V2.Val, V3.Val
>> FROM Vals as V0, Vals as V1, Vals as V2, Vals as V3
>> WHERE
>> V0.SeriesID = 1001 AND AttrID = 5 AND
>> V1.SeriesID = 1002 AND AttrID = 5 AND
>> V2.SeriesID = 1006 AND AttrID = 2 AND
>> V3.SeriesID = 1012 AND AttrID = 1 AND
>> V0.Time = V1.Time AND V0.Time = V1.Time AND V0.Time = V2.Time AND V0.Time
kr> =
>> V3.Time;
How big are the returned set for this query?
Do you really need all the combinations that you get from the above query?
What key do you have? (SeriesID, AttrID, Time) ?
Is this combination unique?
Regards,
Monty
