Nick Ewing wrote:
> searching the database. I have attempted using
> full text searches, but the problem with that is
> that it blocks out words that could be vital to
> looking up a word. I do not have access to my
> main host so I would not be able to recompile it's
> mysql server to fix this problem. I have resorted
> to using the following sql, but it is slow:
>
> SELECT index_id, definition
> FROM dict_definitions
> WHERE definition LIKE '%$search_str%'
> GROUP BY index_id
No wonder it's slow. The database can't use a B-tree index for a query with LIKE [some_pattern]string . MySQL has some optimizations even for this type of queries, but in the end it is a query that can't be efficiently evaluated on a B-tree, especially if you do a search with [some_pattern]string[some_pattern].
If it would be LIKE [some_pattern]string (XABC LIKE %ABC), you could add a new column that holds the reversed string and do LIKE reversed_string[some_pattern] (CBAX LIKE CBA%), but that does not work in your case, because you have patterns before and after the string you're looking for.
BUT: why recompile?
http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html:
"To override the default stopword list, set the ft_stopword_file system variable. (See Section 5.2.2, “Server System Variables”.) The variable value should be the pathname of the file containing the stopword list, or the empty string to disable stopword filtering. After changing the value of this variable or the contents of the stopword file, restart the server and rebuild your FULLTEXT indexes."
(available as of MySQL 4.0.10)
Ulf