Hi!
> I have two tables in the database sites and counter_info where =
> counter_info corresponds to site withe the same id, which is primary =
> key.
> I have a field named t_hosts in the table counter info, on which where =
> is an index.
>
> Then I run guery=20
>
> select * from counter_info order by t_hosts limit 10
>
> I quickly get a result but then i run query
>
> select * from counter_info,sites where sites.id=3Dcounter_info.id order =
> by t_hosts limit 10
>
> This query runs reallu slow. Explane says the followings:
>
> table | type | possible_keys | key | key_len | ref | rows | =
> Extra |
> +--------+--------+---------------+---------+---------+----------+------+=
> -------+
> | sites | ALL | PRIMARY | NULL | NULL | NULL | 6773 =
> | |
> | counter_info | eq_ref | PRIMARY | PRIMARY | 4 | sites.ID | =
> 1 | |
>
>
> so it does not even tries to use the key t_hosts !!!
>
> to make it use the key I try to do the most stupid thing:
>
> alter table counter_info add key tst (id,t_hosts)
> alter table counter_info drop primary key
>
> now it uses key tst some way and runs much faster. The question is why =
> and how ?=20
>
> General question is how to make it work properly.
Sorry, but EXPLAIN doesn't show how the sorting is done.
Next time, pelase post with mysqlbug and include a full description of
your tables. Without knowing what your table look like it's hard to
say what to do.
Anyway, if you have a key defined as (id,t_hosts), it will only be
used for sorting if you do: .. ORDER BY id,t_hosts.
Regards,
Monty
