List:General Discussion« Previous MessageNext Message »
From:Egor Egorov Date:May 26 2003 10:16am
Subject:Re: re: MySQL using filesort :(
View as plain text  
>> 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



Thread
Minimum Requirement to run a MySQL test setupNitin Nanivadekar23 May
  • Re: Minimum Requirement to run a MySQL test setupDan Nelson23 May
  • MySQL using filesort :(Jason Hamilton23 May
    • re: MySQL using filesort :(Egor Egorov23 May
      • re: MySQL using filesort :(Jason Hamilton23 May
        • re: MySQL using filesort :(Jason Hamilton23 May
        • re: MySQL using filesort :(Egor Egorov23 May
          • re: MySQL using filesort :(Jason Hamilton23 May
            • Re: re: MySQL using filesort :(Egor Egorov24 May
              • Defaulting fulltext search to 'AND'?Jason Hamilton24 May
                • Re: Defaulting fulltext search to 'AND'?Victoria Reznichenko26 May
              • Re: re: MySQL using filesort :(Egor Egorov26 May
RE: Minimum Requirement to run a MySQL test setupMike Hillyer23 May