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