Page 1 of 1
search feature for a database
Posted: Fri Jan 19, 2007 2:17 pm
by murlopaz
hi everybody.
I have a database that needs the search functionality.
I've heard about full-text search bulit-in mysql.
Are there any other alternatives?
I thought maybe google would offer an API?
Posted: Sun Jan 21, 2007 7:34 pm
by murlopaz
anybody?
Posted: Sun Jan 21, 2007 7:53 pm
by Luke
sorry to not answer your question, but is there some reason full-text won't work for you?
Posted: Sun Jan 21, 2007 7:56 pm
by murlopaz
I am still trying to figure out if it is good enough for the system...
that's why I am asking you guys for options...
Posted: Sun Jan 21, 2007 7:59 pm
by Luke
Here's a few places you can read up on it:
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://www.devarticles.com/c/a/MySQL/Ge ... abilities/
I haven't yet come across a search that I couldn't accomplish with mysql's full-text. That's not to say it doesn't have its drawbacks, but it works.
Posted: Sun Jan 21, 2007 9:13 pm
by Ollie Saunders
What are you searching through and for? What is the quantity of data, and how quickly do the searches need to be completed? What technologies do you have access to (are you limited by shared hosting for example)? Apply all those questions to the future are they likely to change? If so, how? If you can provide information on all of that it will probably help you, and others, to find something to suit your needs.
Posted: Mon Jan 22, 2007 7:35 am
by murlopaz
ole wrote:What are you searching through and for? What is the quantity of data, and how quickly do the searches need to be completed? What technologies do you have access to (are you limited by shared hosting for example)? Apply all those questions to the future are they likely to change? If so, how? If you can provide information on all of that it will probably help you, and others, to find something to suit your needs.
1. I am searching through a database.
The search form looks like this:
Country: (Dropdown List)
Region: (Dropdown List)
Subject (Dropdown List)
Type of material: (Dropdown List)
Language: (Dropdown List)
Producer (Dropdown List)
Search all fields(Type a word/phrase in this box): (text field)
Note: the last text field searches through every field of a database row. Usually the most important info should be taken from the description field in the database.
Note2: there are probably 15 columns that pertain to this database.
2. There are about 16,000 entries in the db
3. The web server and the db server are dedicated and managed by us, therefore there are no limitations as far as technology goes.
Posted: Mon Jan 22, 2007 9:57 am
by Ollie Saunders
Well from the sounds of it you can just use the LIKE clause.
Code: Select all
SELECT `meh` FROM `table` WHERE `country` LIKE '$country' AND ...
LIKE is case insensitive and because all your fields are dropdowns they can be indexed and the search will run quickly.
Even better (you might want to check out my accuracy on this) is to have the contents of each drop-down in a separate table foreign key referenced wherever you need them by an integer primary key.
Posted: Mon Jan 22, 2007 10:30 am
by murlopaz
well I was thinking about full-text search with boolean capability...
The current system uses the LIKE clause.
Posted: Mon Jan 22, 2007 10:47 am
by Ollie Saunders
with boolean capability
What do you mean by that?
Posted: Mon Jan 22, 2007 10:48 am
by murlopaz
boolean fulltext search
Posted: Mon Jan 22, 2007 10:54 am
by Ollie Saunders
Yeah, I've no idea what you mean by that. The result from LIKE, =, FULLTEXT are all boolean in that they can only return true or false.
Posted: Mon Jan 22, 2007 11:43 am
by murlopaz
Posted: Mon Jan 22, 2007 2:55 pm
by pickle
Doing a fulltext search will return rows in order of relevance - as in, how well they match what you've searched. So, the advantage to doing a fulltext search would be that it would still return results if someone typed in "Canada AB" when the proper field values would be "Canada" and "Alberta". If you want those partial matches to show up, go with fulltext. Just a note though - you'll need to set up an index, which will slow down insert queries (though you probably won't even notice).
Fulltext searching will be faster for you than using LIKE (I believe) as fulltext searching uses an index rather than actually checking each row. If you want to duplicate LIKE functionality, just massage the search terms people use, put '+' in front of each word, & use BOOLEAN mode.
If you do use BOOLEAN mode, make sure you do a sort, as it won't do it for you like non-BOOLEAN fulltext searching will.