SQL search

[Darth Tminos]

I'm new to this, bear with me. I want to create the ability to search a PostgreSQL website for a name of a website inside the database, and then PHP would turn around and present the url in a link and give the description for it. The question is though, what kind of SQL do I use that is not EXACT? running select * from links where name = 'Linux Mafia' will give me the Linuxmafia entry, but if I leave out the space or a capital it won't show. How do I make it loosed up on the criteria?

[stiles]

SELECT * FROM links WHERE Name LIKE '%Linux%' OR Name LIKE '%Mafia%'

might get you started

[klamath]

Yes, I'll have to write something similar to this quite soon. Does anyone have tips searching a large (multi-gigabyte) SQL database for terms? Do you build a separate keyword database?

------------------
- Klamath
Get my GnuPG Key Here

[stiles]

use indexes, slow to write to but fast to read

[Darth Tminos]

Stiles, THANKS!

[klamath]

stiles - yeah, I guess. I'm just a bit cautious to index *gigabytes* of TEXT fields. Each text field could be > 8K. Not only will that cause a lot of extra storage (IIRC, indexes are stored and updated both in the table and a special index file).

I haven't really looked into it. Thanks for the tip.

------------------
- Klamath
Get my GnuPG Key Here

[stiles]

Another option you may consider if indexes are not appropriate for you situation is to write a stored procedure in C (least overhead that way) as your search function and have your middle ware pass a string into the stored proc. for your search variables (nothing in life is free more work no doubt). At least you'll skip the interperter that way.

What's your idea for a keyword database (or do you mean a keyword table)? I don't follow how you plan to seperate keywords form text (I am far from an expert though, from what little I know about your app, this seems redundant to me).

I do have a friend that is very versed in SQL that I can pass this on to him if need be.

[This message has been edited by stiles (edited 08 October 2000).]

[klamath]

My idea of a keyword database is like so:

an 'indexer' looks through the database and finds the location of each word. This word is stored like so:

hello:3,4,5

Which would mean that the word 'hello' was in object IDs 3, 4 and 5. You could also use:

hello:3:1,4:2,5:3

Which would mean 'hello' was found in OID 3 once, OID 4 twice, and OID 5 three times.

(BTW, OID refers to whatever identification scheme you're using, not the internal OIDs used by PgSQL).

When you want to look something up, you just consult the index. I don't know how well it would work, but that's a '5 minute search engine' technique.

------------------
- Klamath
Get my GnuPG Key Here

[jemfinch]

That's really a decent idea, klamath.

You might want to make a full-text substring search the default, and keep statistics on what is searched for -- when something crosses the threshold of "searched occasionally" to "searched never" you could build another table titled <something> which contains those indexes.

Jeremy