• 0

[sql] optimising search of large text field


Question

For 'fun' I've written a little search engine in PHP & MySQL which is generally working pretty nicely. Problem is that it takes ages to fetch the results when you search.

I've removed referrer table from the queries for now to eliminate it from the potential problem causes so my query is something like this:

select * from pages where pagedata like '%searchtext%' and pagedata like '%someotherkeyword%';

I have only around 100,000 records in the table and it takes over 30s to return my results.

Structure of the table is really simple, like this:

table: pages

field: id type: int

field: pageaddress type: varchar(255)

field: pagedata type: text

Is there anything that can be done to optimise this? I know the basics of optimising using indexes, but can't seem to improve the runtime of this at all...

Thanks!

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

First, Remove all commonly used three letter words from your db and modify inserted text to drop these (i.e., drop all "and, or, I, we, etc.." Basically, you want your text table to have distinquishing text that identify it uniquely from all other text entries.

Second, setup a reference table and a nightly triggered routine to populate/update. I'm referring to a table that has something like this:

ID

KEYWORD

LINE_ID

And, populate as such:

1 MAINTENANCE "7,14,33,34,97"

2 MODERATOR "22,23,66,67,103"

Where you first search this table, thus gathering the comma del. string of row numbers for the text table.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.