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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.