How fast is this query?
SELECT id
FROM msg_body
WHERE MATCH(body) AGAINST( 'WORD')
If it's fast , you may have to re-state your query so that you are not FT
searching and joining tables in the same statement. The optimizer can only
use 1 index at a time from any table. We may be running into a conflict
about which index to use.
You might break up your query like this in order to get better speed:
CREATE TEMPORARY TABLE tmpBody
SELECT id
FROM msg_body
WHERE MATCH(body) AGAINST( 'WORD')
CREATE TEMPORARY TABLE tmpMsg
SELECT ID
FROM msg_header
WHERE MATCH (list) against ('listname')
ALTER TABLE tmpBody ADD KEY(id)
ALTER TABLE tmpMsg ADD KEY(id)
SELECT
h.bodyid
, h.id
, h.subject
, h.mfrom
, h.date
, msg_header.list
FROM tmpMsg tm
INNER JOIN msg_header h
ON h.id = th.id
INNER JOIN tmpBody tb
on h.bodyid = tb.id
The two FT searches should happen quickly ( < 10 seconds each), Adding
indices to both temp tables could take up to 2 seconds each. The final
query is fully indexed so it should return <2 seconds. This gives us a
worst-case scenario of 26 seconds. However, I would guess that you get
sub-tens during actual testing. Since I opted to use temporary tables they
will be unique per connection so you will not need to worry about name
collisions during concurrent executions. I would still "DROP TABLE tmpMsg,
tmpBody" before closing the connection just to make sure those resources
are released as soon as possible.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Fredrik Carlsson <[EMAIL PROTECTED]> wrote on 08/20/2004 01:13:40
PM:
> 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]
>
