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]
