>> At 11:36 AM 5/23/2003, Egor Egorov wrote:
>>
>> Please show me the output of SHOW CREATE TABLE and the exactly output of EXPLAIN
> SELECT.
>Here is the "broken" query:
>
>SQL-query: "EXPLAIN SELECT * FROM chan LEFT JOIN network ON chan.network_id =
> network.id WHERE chan.network_id = '21' ORDER BY chan.current_users"
>|table |type |possible_keys|key |key_len|ref |rows |Extra
>|chan |ref |network_id |network_id|2 |const |47476|Using where;
> Using filesort
>|network|eq_ref |PRIMARY,id |PRIMARY |2 |chan.network_id|1 |
Look at the indexes in the table 'chan'
> KEY `chan` (`chan`(5)),
> KEY `current_users` (`current_users`),
> KEY `network_id` (`network_id`),
> KEY `last_seen` (`last_seen`),
> KEY `seen_count` (`seen_count`),
> KEY `avg_users` (`avg_users`),
There is no compound index on both network_id and current_users columns. Create an index
on (network_id, current_users).
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
