Fulltext search help

Get help on web editors (Frontpage, Dreamweaver) and web languages (HTML, ASP, PHP).
Post Reply
DarkRanger
Registered User
Posts: 8346
Joined: 10 May 2006, 02:00
Processor: Intel i5-3750
Motherboard: Gigabyte
Graphics card: nVidia GTX 550Ti
Memory: 8GB Jetram
Contact:

Fulltext search help

Post by DarkRanger »

Ok so I read up a bit on fulltext search. Sure, the document is from 2004, so a lot might have changed. That is why I'm asking here.

I have a database with various tables. I want to search through the database and sort via relevance. I've learned that fulltext search will make my job very simple. I then decided I'd make fulltext search easier, and just smash everything into one table, with that ID linking to other tables. So basically I've got this:

Code: Select all

Table: fileKeywords
,------,--------------------------------------------------,
| ID   | keywords                                         |
|------|--------------------------------------------------|
| x    | *content of row*                                 |
'------'--------------------------------------------------'
The ID in this table links to id's in other tables, but all the contents of the other tables are in here so no need to worry about that.

What I'm interested in is the fulltext query I'll have to create. From my understanding, it'll have to be like this:

Code: Select all

SELECT id FROM fileKeywords WHERE MATCH (keywords) AGAINST (query WITH QUERY EXPANSION) AS score FROM fileKeywords WHERE MATCH (keywords) AGAINST (query);
Is this correct? Or am I missing something?

I'm new to this search thing...
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Fulltext search help

Post by hamin_aus »

DarkRanger wrote:I have a database with various tables. I want to search through the database and sort via relevance. I've learned that fulltext search will make my job very simple. I then decided I'd make fulltext search easier, and just smash everything into one table, with that ID linking to other tables.
I don't know exactly what you are attempting to do, but it sounds amusing.

You need to elaborate on this for me.

What exactly do you want to search? the contents of all the tables in your DB :?: That's what it sounds like.

So how does creating a table with an id column and then another column with the contents of a table "smashed" in help? How exactly do you smash a table into a column. I'm interested to know so I can give the developers at my company new and exciting ways to torture me.

You could have a look at keyname values, this could be a way of getting any amount of rows into a single column. You should be able to then do full-text indexing on that hot mess...

Also, you do know that your table will have to be MyISAM to do full-text indexing?

You can kiss row-level locking goodbye. You might as well be writing to a text file after that, it'll only do one write at a time to the table. Not really an issue if you will mostly be reading from this table, but do keep that in mind.

I have never had to do something as involved as full-text indexing in MySQL, so I probably wont be able to help you with the actual nitty-gritty - but do let me know how it goes.
Image
Post Reply