Result of explain:
mysql> explain select sum(size) from message where uid='2945';
+---------+------+---------------+------+---------+-------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+-------+------+-------------+
| message | ref | uid | uid | 4 | const | 2491 | Using where |
+---------+------+---------------+------+---------+-------+------+-------------+
Ganbold
At 12:54 PM 17.10.2003 +0300, you wrote:
Ganbold <[EMAIL PROTECTED]> wrote:
>
> I'm having some trouble running one query. I'm using FreeBSD 4.8 with
> linuxthread enabled mysql-4.0.14.
> Server has 1GB ram and SCSI hard disk.
>
> I need to get size of email message which is stored in MyISAM table.
>
> The problematic query is:
>
> select sum(size) from message where uid='2945';
>
> -------------------------------------------------------------------------------------------------------------------------------------------------
> mysql> select sum(size) from message where uid='2945';
> +-----------+
> | sum(size) |
> +-----------+
> | 249722888 |
> +-----------+
> 1 row in set (35.04 sec)
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> This query took me 35 seconds. Message table has 286867 rows and 4.7GB of size.
>
>
> uid field is indexed as well as some others.
uid is a part of index. Is uid the first part of compound index? Use EXPLAIN to see if MySQL uses index.
>
> Table structure:
> -------------------------------------------------------------------------------------------------------------------------------------------------
> mysql> describe message;
> +-------+---------------------------+---------+------+-----------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+---------------------------+---------+------+-----------+----------------+
> | sjt | tinytext | YES | | NULL | |
> | uid | int(1) unsigned | | MUL | 0 | |
> | mto | varchar(255) | YES | MUL | NULL | |
> | mfr | varchar(255) | YES | MUL | NULL | |
> | msg | longtext | YES | | NULL | |
> | date | int(1) | YES | MUL | NULL | |
> | dir | char(1) | YES | MUL | NULL | |
> | stat | enum('N','O','S','D') | YES | MUL | NULL | |
> | id | int(1) unsigned | | PRI | NULL | auto_increment |
> | size | int(1) | YES | MUL | NULL | |
> +-------+--------------------------+---------+-------+-----------+----------------+
> 10 rows in set (0.00 sec)
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
-- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
