I think, if I understand this right, that the problem is the
int(1) which limits the index to only the first digit,
try to change that to int(11) or something like that, and see if that cures it.
Kelley
Ganbold wrote:
> Hi,
>
> 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.
>
> -------------------------------------------------------------------------------------------------------------------------------------------------
> mysql> show table status like 'mes%';
> +---------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
> | Name | Type | Row_format | Rows | Avg_row_length | Data_length |
> Max_data_length | Index_length | Data_free | Auto_increment |
> Create_time | Update_time | Check_time |
> Create_options | Comment |
> +---------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
> | message | MyISAM | Dynamic | 286867 | 16277 | 4733333928 |
> 281474976710655 | 41110528 | 63864352 | 1373513 | 2003-10-15
> 20:11:19 | 2003-10-17 10:01:31 | 2003-10-17 06:03:44 | max_rows=4294967295
> avg_row_length=400 | |
> +---------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> uid field is indexed as well as some others.
>
> 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)
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Following is the /etc/my.conf
> -------------------------------------------------------------------------------------------------------------------------------------------------
> [mysqld]
> port = 3306
> socket = /tmp/mysql.sock
> skip-locking
> set-variable = key_buffer=384M
> set-variable = max_allowed_packet=32M
> set-variable = table_cache=512
> set-variable = sort_buffer=2M
> set-variable = record_buffer=2M
> set-variable = thread_cache=64
> set-variable = thread_concurrency=4
> set-variable = myisam_sort_buffer_size=64M
> set-variable = max_connections=5000
> set-variable = query_cache_size=8M
> set-variable = query_cache_limit=8M
>
> [mysqldump]
> quick
> set-variable = max_allowed_packet=100M
>
> [mysql]
> no-auto-rehash
>
> [isamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [myisamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [mysqlhotcopy]
> interactive-timeout
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> How can I speed up this query? Can somebody help me?
>
> thanks in advance,
>
> Ganbold Ts.
>
> --
> 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]
