List:General Discussion« Previous MessageNext Message »
From:Fredrik Carlsson Date:August 20 2004 5:13pm
Subject:Fulltext performance problem.
View as plain text  
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





Thread
Fulltext performance problem.Fredrik Carlsson20 Aug
  • Re: Fulltext performance problem.Thomas Spahni23 Aug
  • Re: Fulltext performance problem.SGreen24 Aug