>>>>> "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
