List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 11:00pm
Subject:Strange perfomance
View as plain text  
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
Thread
Strange perfomancePeter Zaitsev12 Oct
  • Strange perfomanceMichael Widenius13 Oct