List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 11 1999 11:56am
Subject:Re: Perfomance trouble again.
View as plain text  
>>>>> "sinisa" == sinisa  <sinisa@stripped> writes:

<cut>

pz> Then I run the following query it goes pretty fast ( 0.02-0.05 sec)
pz> select t_hits from counter_info order by t_hits limit 10;

pz> but this query which should be just a bit slower runs really _S_L_O_W_
pz> select name,t_hits from sites,counter_info where id=3Dcounter_id order =
pz> by t_hits limit 10;
pz> 10 rows in set (0.60 sec)

pz> I'm sure the index is not used to optimize order by and limit because=20
pz> select name,t_hosts from sites,counter_info where id=3Dcounter_id order =
pz> by t_hosts limit 10;
pz> 10 rows in set (0.63 sec)

pz> So these queries run about the same time -> index is not used there it =
pz> is should :(

pz> Optimize says the followings:

pz> explain select name,t_hits from sites,counter_info where id=3Dcounter_id =
pz> order by t_hits limit 10; =20

pz> +--------------+--------+---------------+---------+---------+----------+-=
pz> -----+-------+
pz> | table        | type   | possible_keys | key     | key_len | ref      | =
pz> rows | Extra |
pz> +--------------+--------+---------------+---------+---------+----------+-=
pz> -----+-------+
pz> | sites        | ALL    | PRIMARY       | NULL    |    NULL | NULL     | =
pz> 6643 |       |
pz> | counter_info | eq_ref | PRIMARY       | PRIMARY |       4 | sites.ID | =
pz> 1 |       |
pz> +--------------+--------+---------------+---------+---------+----------+-=
pz> -----+-------+
pz> 2 rows in set (0.00 sec)


sinisa> Is there any way I can make such queries running faster ?

sinisa> This is a bug in mysql optimizer. 

sinisa> Look for new releases in the following months, because it is on a TODO 
sinisa> to be fixed.

Hi!

Sinisa is wrong in this case;  There isn't any problems with the
optimizer in this case (and there is nothing on specific in the TODO
section concerning this)

Try this:

select name,t_hits from counter_info straight_join sites where id=counter_id
order by t_hits limit 10

(This will force MySQL to first use the table counter_info, where the
t_hits fields is)

Regards,
Monty
Thread
Perfomance trouble again.Peter Zaitsev21 Oct
  • Re: Perfomance trouble again.sinisa21 Oct
    • Re: Perfomance trouble again.Michael Widenius11 Nov