I have a table with 2.9 mil records which represents 197k sentences stored
vertically. I do this because I need to know information about each word
and its relationship to other tables.
I want to know how many words have a particular word-group ID before and a
particular word-group ID after the word ID I am looking for. All of the
fields shown are indexed integer fields.
The examples below actually work fine but Word ID #8 happens to be a popular
word ('the' 216,000) and this query takes 1 min 15 sec to run. When the
Word ID I am looking for is not so popular it executes in 0.05 - 5.0
seconds. Basically, 90% of the time its OK but 10% of the time is taking
longer than the 90%.
One of the things I noticed with the server is that the CPU and disk access
is virtually nothing while these joins are running. Other parts of my
program can take the CPU near 100% when running multiple copies but when any
of those copies get to this Inner Join it is slow and is tagged as a MySQL
Slow query.
Are there settings I should be making on the Server?
Is there a better way or method to make a query like this go faster?
Thanks
Butch Bean
Table type is MyISAM
EXPLAIN returns the following:
+---------------+------+------------------------------+----------+---------+
---------------------+------+------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------------+------+------------------------------+----------+---------+
---------------------+------+------------+
| tbl_SENT | ref | SENT_ID,Grp | Grp | 4 |
const | 4295 | where used |
| tbl_SENT_1 | ref | SENT_ID,WORD_ID,WORD_POSITION| SENT_ID | 5 |
tbl_SENT.SENT_ID | 15 | where used |
| tbl_SENT_2 | ref | SENT_ID,WORD_POSITION,Grp | SENT_ID | 5 |
tbl_SENT.SENT_ID | 15 | where used |
+---------------+------+------------------------------+----------+---------+
---------------------+------+------------+
SELECT count(*) FROM tbl_SENT
INNER JOIN tbl_SENT AS tbl_SENT_1
ON tbl_SENT.SENT_ID = tbl_SENT_1.SENT_ID
INNER JOIN tbl_SENT AS tbl_SENT_2
ON tbl_SENT.SENT_ID = tbl_SENT_2.SENT_ID
WHERE tbl_SENT_1.WORD_POSITION=tbl_SENT.WORD_POSITION + 1
AND tbl_SENT_2.WORD_POSITION=tbl_SENT.WORD_POSITION + 2
AND tbl_SENT.Grp=28744
AND tbl_SENT_1.WORD_ID=8
AND tbl_SENT_2.Grp=60072)
***** OR *****
SELECT count(*) FROM tbl_SENT
INNER JOIN tbl_SENT AS tbl_SENT_1
ON tbl_SENT.SENT_ID = tbl_SENT_1.SENT_ID
INNER JOIN tbl_SENT AS tbl_SENT_2
ON tbl_SENT.SENT_ID = tbl_SENT_2.SENT_ID
WHERE ((tbl_SENT.WORD_ID=8
AND (tbl_SENT_1.WORD_POSITION=tbl_SENT.WORD_POSITION - 1
AND tbl_SENT_1.Grp=28744))
AND (tbl_SENT_2.WORD_POSITION=tbl_SENT.WORD_POSITION + 1
AND tbl_SENT_2.Grp=60072))
***** OR *****
SELECT count(*) FROM tbl_SENT
INNER JOIN tbl_SENT AS tbl_SENT_1
ON (tbl_SENT.SENT_ID = tbl_SENT_1.SENT_ID) AND
(tbl_SENT_1.WORD_POSITION=tbl_SENT.WORD_POSITION - 1)
AND (tbl_SENT_1.Grp=28744)
INNER JOIN tbl_SENT AS tbl_SENT_2
ON (tbl_SENT.SENT_ID = tbl_SENT_2.SENT_ID) AND
(tbl_SENT_2.WORD_POSITION=tbl_SENT.WORD_POSITION + 1)
AND (tbl_SENT_2.Grp=60072)
WHERE tbl_SENT.WORD_ID=8
***** OR *****
SELECT count(*) FROM tbl_SENT AS tbl_SENT_0, tbl_SENT AS tbl_SENT_1,
tbl_SENT AS tbl_SENT_2
WHERE (tbl_SENT_0.SENT_ID = tbl_SENT_1.SENT_ID
AND tbl_SENT_0.SENT_ID = tbl_SENT_2.SENT_ID)
AND (tbl_SENT_1.WORD_POSITION=tbl_SENT_0.WORD_POSITION + 1
AND tbl_SENT_2.WORD_POSITION=tbl_SENT_0.WORD_POSITION + 2
AND tbl_SENT_0.Grp=28744
AND tbl_SENT_1.WORD_ID=8
AND tbl_SENT_2.Grp=60072)
Server:
Dual PIII 800 w/1gb RAM - Win2K
join_buffer_size = 100M (does not seem to effect this query)
set-variable = key_buffer=700M
set-variable = max_allowed_packet=2M
set-variable = join_buffer_size=100M
set-variable = table_cache=512M
set-variable = sort_buffer=20M
set-variable = record_buffer=20M
set-variable = thread_cache=30M
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
