search feature for a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

search feature for a database

Post 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?
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post by murlopaz »

anybody?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

sorry to not answer your question, but is there some reason full-text won't work for you?
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post 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...
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post by murlopaz »

well I was thinking about full-text search with boolean capability...
The current system uses the LIKE clause.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

with boolean capability
What do you mean by that?
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post by murlopaz »

boolean fulltext search
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
murlopaz
Forum Commoner
Posts: 60
Joined: Wed Oct 11, 2006 5:02 pm
Location: Baltimore, MD, USA

Post by murlopaz »

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply