Hi Fredrik,
a LEFT JOIN could be faster (I'm not sure, try it). And you don't have a
fulltext index on msg_header.list.
What about this?
SELECT msg_header.bodyid, msg_header.id, msg_header.subject,
msg_header.mfrom, msg_header.date, msg_header.list
FROM msg_header
LEFT JOIN msg_body ON msg_header.bodyid = msg_body.id
WHERE msg_header.list LIKE 'LISTNAME%'
AND MATCH(msg_body.body) AGAINST('WORD');
Regards, Thomas Spahni
On Fri, 20 Aug 2004, Fredrik Carlsson wrote:
> 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]
