Re: Fulltext performance problem.
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 t
Re: Fulltext performance problem.
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?
SELECTmsg_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
>
>
>
>
>
>
>
Fulltext performance problem.
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]
