Hi all,
I'm running a small mail archive and have a little problem with the fulltext search performance.
I really appreciate any tips/design suggestions (even if it dont have to do with the search problem ;) ).
Database schema:
mysql> describe msg_header; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | parent | int(11) | YES | MUL | NULL | | | bodyid | int(11) | YES | | NULL | | | list | varchar(80) | YES | MUL | NULL | | | mfrom | varchar(80) | YES | | NULL | | | mto | varchar(80) | YES | | NULL | | | subject | varchar(200) | YES | MUL | NULL | | | mcc | varchar(80) | YES | | NULL | | | sdate | varchar(45) | YES | | NULL | | | batch | varchar(80) | YES | MUL | NULL | | | msgid | varchar(90) | YES | | NULL | | | date | datetime | YES | MUL | NULL | | +---------+--------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec)
mysql> describe msg_body; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | body | text | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
index from msg_body;
*************************** 1. row *************************** Table: msg_body Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 295996 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: msg_body Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 295996 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: msg_body Non_unique: 1 Key_name: body Seq_in_index: 1 Column_name: body Collation: A Cardinality: 295996 Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: 3 rows in set (0.00 sec)
The search querys using fulltext indexes takes around > 1minute and no one want to use a search that slow :/
The Query is the following:
SELECT msg_header.bodyid,msg_header.id, msg_header.subject,msg_header.mfrom, msg_header.date, msg_header.list FROM
msg_header,msg_body WHERE msg_header.bodyid=msg_body.id AND match(msg_header.list) against('LISTNAME')
AND match(msg_body.body) AGAINST('WORD');
For a couple of month ago the msg-body and msg-headers parts where in the same table and the fulltext search was really fast < 1 sec, but everything else just became slower so i splitted it upp in two tables. But now i need to match msg_header.bodyid against msg_body.id to be able to now witch body that belongs to with header and i think thats where things get slow..
I ran an explain select query and the following result turned upp
+------------+----------+-----------------+---------+---------+-------------------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+----------+-----------------+---------+---------+-------------------+------+-------------+
| msg_header | fulltext | list_4 | list_4 | 0 | | 1 | Using where |
| msg_body | eq_ref | PRIMARY,id,body | PRIMARY | 4 | msg_header.bodyid | 1 | Using where |
+------------+----------+-----------------+---------+---------+-------------------+------+-------------+
Does this means that the fulltext index on msg_body.body is'nt being used?
The machine is an Intel PIII 500Mhz, 512MB memory and IDE disks running NetBSD 1.6.3, mysql 4.0.20.
The database contains about 300 000 rows and the size is ~1GB.
// Fredrik Carlsson
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
