List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 12 2000 11:21pm
Subject:RE: speed peculiarity
View as plain text  
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

Thread
speed peculiarityKristo Käärmann7 Jan
  • Re: speed peculiaritySasha Pachev7 Jan
    • RE: speed peculiarityKristo Käärmann8 Jan
      • RE: speed peculiarityMichael Widenius13 Jan
        • Question...Fraser MacKenzie13 Jan
          • Re: Question...Benjamin Pflugmann13 Jan
    • RE: speed peculiarityKristo Käärmann18 Jan
      • Re: speed peculiaritysasha18 Jan
        • RE: speed peculiarityKristo Käärmann19 Jan
          • RE: speed peculiaritysinisa19 Jan
          • Re: speed peculiarityTonu Samuel21 Jan