You neglected to say how much memory your system has.
I am a newbie, but one of the first things I learned about mysql, is
that it needs a lot of memory to handle any kind of load. If you do
not have at least 500meg, add some.
On 26 Feb 2001, at 13:22, Joseph Bueno wrote:
> Hi list,
>
> I have some trouble trying to optimise SELECT queries
> that use ORDER BY condition.
>
> I have found out that each time the server is busy
> (100% CPU usage), it is due to queries that show
> up in 'mysqladmin processlist' report with 'Sorting result'
> state in 'Command' column.
>
> I am trying to optimise it by adding proper indexes so
> that these sorts could be avoided.
>
> According to MySQL manual (12.4 How MySQL Uses Indexes),
>
> SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3;
> SELECT * FROM foo WHERE key_part1=const ORDER BY key_part2;
>
> should use an index to avoid sorting
> (second SELECT is actually a GROUP BY in the manual but I
> assume it applies also to ORDER BY since the manual makes
> no distinction between them).
>
> In my case, sort is still used.
>
> My table looks like:
>
> CREATE TABLE pa (
> region int(11) NOT NULL default '0',
> code int(11) NOT NULL default '0',
> pseudo varchar(20) NOT NULL default '',
> date_depose int(11) NOT NULL default '0',
> nom varchar(80) NOT NULL default '',
> pays int(11) NOT NULL default '0',
> commentaire text NOT NULL,
> PRIMARY KEY (code),
> UNIQUE KEY idx1(pseudo),
> KEY date_depose(date_depose),
> KEY pays_date_depose(pays,date_depose)
> ) TYPE=MyISAM;
>
> and here are some EXPLAIN:
>
> mysql> explain select code from pa order by date_depose desc;
> +-------+------+---------------+------+---------+------+--------+-----
> +-----------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +-------+------+---------------+------+---------+------+--------+-----
> -----------+ | pa | ALL | NULL | NULL | NULL | NULL |
> 151826 | Using filesort |
> +-------+------+---------------+------+---------+------+--------+-----
> -----------+ 1 row in set (0.00 sec)
>
> mysql> explain select code from pa order by date_depose desc limit
> 2000;
> +-------+-------+---------------+-------------+---------+------+------
> --+-------+ | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +-------+-------+---------------+-------------+---------+------+------
> --+-------+ | pa | index | NULL | date_depose | 4 |
> NULL | 151826 | |
> +-------+-------+---------------+-------------+---------+------+------
> --+-------+ 1 row in set (0.00 sec)
>
> It seems that the index is used in second case only; may be because of
> LIMIT option; but this is NOT what is described in the manual.
>
> mysql> explain select code from pa where pays=2 order by date_depose
> desc;
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ | table | type | possible_keys
> | key | key_len | ref | rows | Extra
> |
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ | pa | ref |
> pays_date_depose | pays_date_depose | 4 | const | 1178 | where
> used; Using filesort |
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ 1 row in set (0.00 sec)
>
>
> mysql> explain select code from pa where pays=2 order by date_depose
> desc limit 100;
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ | table | type | possible_keys
> | key | key_len | ref | rows | Extra
> |
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ | pa | ref |
> pays_date_depose | pays_date_depose | 4 | const | 1178 | where
> used; Using filesort |
> +-------+------+------------------+------------------+---------+------
> -+------+----------------------------+ 1 row in set (0.01 sec)
>
> In this case, the index is NEVER used to avoid sorting even when LIMIT
> option is restricting the result set.
>
> I am currently running MySQL version 3.23.30-gamma, but I have
> checked release notes for more recent versions I have not found
> anything related to this.
>
> What can I do to make these queries less CPU hungry ?
>
> Thanks
> --
> Joseph Bueno
> NetClub/Trader.com
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]> To
> unsubscribe, e-mail
> <[EMAIL PROTECTED]> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
John Jensen
520 Goshawk Court
Bakersfield, CA 93309
661-833-2858
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
