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]
