fast keyword search - suggestions asked

[Torfi Sackbatten]

Hi Everyone,

I´m asked to "speed up" a keyword search based on MySQL.
The material i´m given to work with is a quite large MySQL table with
1.5 mio rows, defined something like:

CREATE TABLE datarecords (
id BIGINT(20) NOT NULL auto_increment,
[3-4 other INT type columns],
keywords TEXT NOT NULL,
updated TIMESTAMP(14) NOT NULL,
PRIMARY KEY (id)) TYPE=MyISAM;

The search requires the id-fields of the datarecords table to be
returned, based on keywords within the keywords-field, like so:

SELECT id FROM datarecords WHERE (
(keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
AND
(keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
'%thisneither%' )
)

The table has grown unexpectedly large and will keep growing, so a
different method for keyword searching appears to be required, since
search-speed has decreased significantly (now at about 12 secs,
depending an the number of searched keywords)

Using FULLTEXT indexing has brought no performance increase...

Question:
Does anybody have any suggestion on how to tackle this type of
problem, to get search-speed down to about 2-3 secs?

One way i´m looking at this is to create a seperate keyword-table
something like this:

CREATE TABLE testkwdlist (
keyword varchar(32) NOT NULL default '',
ids text NOT NULL,
PRIMARY KEY (keyword)
) TYPE=MyISAM;

and filling this table up with the keywords and listing the IDs in the
ids-field, doing seperate queries for the positive and negative
searches and compiling the actual resultset in code...

Does this sound it has potential for a performance boost or is it
competely insane?

Any comments gladly appreciated! I will try out and report about any
sane suggestion!

Thanks in advance,

Torfi

Some basis-data:
MySQL 4.0.15, Suse 9.0, kernel 2.4.21-215-smp4G
on dual 3,6GHz XEON, 4GB RAM, enough disk.

[Wouter]

I would suggest to real "select statement needed"
started a few days a go on this newsgroup.

I'll test the method next week (I hope).

Wouter
"Torfi Sackbatten" <to*******@exsi teing.com> wrote in message
news:96******** *************** ***@posting.goo gle.com...
: Hi Everyone,
:
: I´m asked to "speed up" a keyword search based on MySQL.
: The material i´m given to work with is a quite large MySQL table with
: 1.5 mio rows, defined something like:
:
: CREATE TABLE datarecords (
: id BIGINT(20) NOT NULL auto_increment,
: [3-4 other INT type columns],
: keywords TEXT NOT NULL,
: updated TIMESTAMP(14) NOT NULL,
: PRIMARY KEY (id)) TYPE=MyISAM;
:
: The search requires the id-fields of the datarecords table to be
: returned, based on keywords within the keywords-field, like so:
:
: SELECT id FROM datarecords WHERE (
: (keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
: AND
: (keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
: '%thisneither%' )
: )
:
: The table has grown unexpectedly large and will keep growing, so a
: different method for keyword searching appears to be required, since
: search-speed has decreased significantly (now at about 12 secs,
: depending an the number of searched keywords)
:
: Using FULLTEXT indexing has brought no performance increase...
:
: Question:
: Does anybody have any suggestion on how to tackle this type of
: problem, to get search-speed down to about 2-3 secs?
:
: One way i´m looking at this is to create a seperate keyword-table
: something like this:
:
: CREATE TABLE testkwdlist (
: keyword varchar(32) NOT NULL default '',
: ids text NOT NULL,
: PRIMARY KEY (keyword)
: ) TYPE=MyISAM;
:
: and filling this table up with the keywords and listing the IDs in the
: ids-field, doing seperate queries for the positive and negative
: searches and compiling the actual resultset in code...
:
: Does this sound it has potential for a performance boost or is it
: competely insane?
:
: Any comments gladly appreciated! I will try out and report about any
: sane suggestion!
:
: Thanks in advance,
:
: Torfi
:
: Some basis-data:
: MySQL 4.0.15, Suse 9.0, kernel 2.4.21-215-smp4G
: on dual 3,6GHz XEON, 4GB RAM, enough disk.

[Wouter]

: I would suggest to real "select statement needed"
: started a few days a go on this newsgroup.

I mean read articles with the subject "select statement needed"

seems I can't type well today.
Wouter