[SOLVED] Search Engine

[Yacoby]

I have a mysql database with about 50,000 rows, that I want the user to be able to search.

Two of the columns contain text that is on average abouts 3 words long.

The third column contains any amount of text, usually a couple of paragraphs.

 

I would just use mysqls full text search, however, on my (cheap) hosting, the minimum indexed word length is 4, and I can't change it, and the user has to be able to search for words less than 4 characters on the two smaller columns.

 

At present, I search the table by building up a SELECT FROM ... WHERE ... LIKE %...% ... statement from the keywords, and then ranking them/excluding them by searching every result for the number of occurrences of the search terms.

 

 

Is there any method that you could advise that would be better than that, or is that the best I can do?

 

 

 

[JonnoTheDev]

A much better method would be to use a full text search engine rather than your database. On a large number of records using a query like SELECT FROM ... WHERE ... LIKE %...% will be slow.

Building a search index is much more efficient and can rank your search results as well as do plenty of other stuff.

However seen as you are on a shared hosting account you may have trouble installing these tools. I would opt for better web hosting and go for a dedicated server.

 

http://sphinxsearch.com/

http://framework.zend.com/manual/en/zend.search.lucene.html

[Yacoby]

However seen as you are on a shared hosting account you may have trouble installing these tools. I would opt for better web hosting and go for a dedicated server.

I would prefer to work within the existing constraints of my current host as it is far cheaper.

 

I may go and see if they can/there is a possibility they will

 

A much better method would be to use a full text search engine rather than your database. On a large number of records using a query like SELECT FROM ... WHERE ... LIKE %...% will be slow.

Building a search index is much more efficient and can rank your search results as well as do plenty of other stuff.

Given that for the two smaller columns, I can't use the mysql built in full text search, would it be better to write some custom code to build an index in another table for these columns, and then do searches using that.

I realize it wouldn't be close to the performance of compiled code with a decent data structure designed by people who know what they are doing, but would it be likely to be faster than what I am doing at present?

 

I could probably use MySQLs full text search on the larger column. (However bad it may be).

 

 

 

Thanks for your comments, and especially the links to Sphinx et al.

[kickstart]

Hi

 

While a full text search would not be that efficient, you could possibly improve the performance of it by limiting the search to rows which you know contain the words (if not in the right order).

 

Have a table of words and the rows that they are in, and build up a subselect to get those rows.

 

Eg:-

 

WordTable

Id

Word

RowId

 

SELECT *

FROM FullTable

WHERE TextField like "%first second third fourth%"

AND RowId IN

(SELECT a.RowId

FROM WordTable a

JOIN WordTable b ON a.RowId = b.RowId

JOIN WordTable c ON a.RowId = c.RowId

JOIN WordTable d ON a.RowId = d.RowId

WHERE a.Word = "first"

AND a.Word = "second"

AND a.Word = "third"

AND a.Word = "fourth")

 

The subselect should only bring back any RowId which contains all the words specified.

 

I am not sure that would be more efficient, but I would give it a try.

 

All the best

 

Keith

[JonnoTheDev]

Given that for the two smaller columns, I can't use the mysql built in full text search

I was not talking about adding a fulltext index to your tables text fields but using a Full Text Search Engine instead of using your database at all. This builds an index from your database tables and the index created gets searched rather than the database making it much faster and taking the load away from the database.

Think of Google. When you search Google you are searching their index. You are not searching through a database containing millions of urls (just think how long that would take to return results).

 

If you want to stick with your hosting then it should be possible for you to upload the Zend Framework to your webspace. Then I would make use of Lucene.

 

http://framework.zend.com/manual/en/zend.search.lucene.html

 

Upto you but creating tables with thousands of records and using the LIKE operator will be slow. If this is a search field on your website that users will be using then that may be sat there waiting a long time for results.

[Yacoby]

That works, I should have payed more attention to the Zend framework link. Many thanks