Fulltext search help

[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:

 

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:

 

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...

[fenway]

Why do you have "FROM" twice?

[DarkRanger]

Ugh, I copied something and edited and then decided the other one will work better and copied something again. Sorry, bad typing there.

 

SELECT id MATCH (keywords) AGAINST ('query' WITH QUERY EXPANSION) AS score FROM fileKeywords WHERE MATCH (keywords) AGAINST ('query');

 

^^ Okay, would that work?

[ManiacDan]

Putting a match/against condition in your WHERE clause automatically sorts the results by relevance, and therefore you don't need the score (nobody displays it anymore anyway).  Also, your query is wrong because there's no comma after ID, but again I'm recommending cutting out the whole first match/against clause anyway.

 

You need this table to be MyISAM type (others are supported however), and you need a FULLTEXT index on this column.

 

You also need to put your search modifiers (IN BOOLEAN MODE and/or WITH QUERY EXPANSION) into your WHERE, not your SELECT.

 

Final note: Your table needs to have the ID plus the foreign table name.  What happens when there's 3 entries for ID 2?  Which table do they reference?

[DarkRanger]

First of, thanks for the response.

 

Putting a match/against condition in your WHERE clause automatically sorts the results by relevance, and therefore you don't need the score (nobody displays it anymore anyway).  Also, your query is wrong because there's no comma after ID, but again I'm recommending cutting out the whole first match/against clause anyway.

 

Okay I wasn't sure about that so I'll remove the first part.

 

You need this table to be MyISAM type (others are supported however), and you need a FULLTEXT index on this column.

 

MyISAM type? Suppose a Google is in order. On which column should the FULLTEXT index be? On the keywords column?

 

You also need to put your search modifiers (IN BOOLEAN MODE and/or WITH QUERY EXPANSION) into your WHERE, not your SELECT.

 

I will change that. I assumed as much after you said I need to remove the first part.

 

Final note: Your table needs to have the ID plus the foreign table name.  What happens when there's 3 entries for ID 2?  Which table do they reference?

 

The id in this table corresponds to other table's "file" column. So I use the id that I get here, to retrieve data from other tables that have that value under their "file" column. I understand what you are saying though, so I'll have a look at this too.

 

Again, thanks for the response!! 

[ManiacDan]

MyISAM type? Suppose a Google is in order. On which column should the FULLTEXT index be? On the keywords column?

Tables are stored with a particular storage engine.  The two most popular are InnoDB and MyISAM.  InnoDB doesn't support fulltext searching.

 

The index has to be on the column you're searching, yes.

 

 

The id in this table corresponds to other table's "file" column. So I use the id that I get here, to retrieve data from other tables that have that value under their "file" column. I understand what you are saying though, so I'll have a look at this too.

You have multiple tables all storing the same data?  Why?  Or are you saying that there are multiple sets of data that all reference a single "file" table?  If that's the case, that's good.

[DarkRanger]

The id in this table corresponds to other table's "file" column. So I use the id that I get here, to retrieve data from other tables that have that value under their "file" column. I understand what you are saying though, so I'll have a look at this too.

You have multiple tables all storing the same data?  Why?  Or are you saying that there are multiple sets of data that all reference a single "file" table?  If that's the case, that's good.

Multiple sets of data all reference a single file table.  Sorry if it's confusing. English isn't my first language! 

 

Thanks for all the help. I'll give it a bash tomorrow and report back! (South African time now is 18:37, this prompts Forza 4 and Battlefield 3 time. Workday is over! )

[QuickOldCar]

Here is an example select using as what ManiacDan suggested

 

$result = mysql_query("SELECT * FROM table_name WHERE MATCH (column_name) AGAINST ('$trimmed_escaped_search_words' IN BOOLEAN MODE) ORDER BY ID DESC Limit $startrow,$posts_per_page");

 

Create an index an any columns used in the WHERE clause

You could simply set the LIMIT to 100, you would not want all of them if many results, you should paginate the results.

 

Can also lower the minimum word length for search results.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html

Located in my.ini for MYSQL

[mysqld]

ft_min_word_len=3

 

then repair the tables after restart of the server

REPAIR TABLE tbl_name QUICK;

 

As for the $trimmed_escaped_search_words

directly before each search word can be operators

+ stands for AND

- stands for NOT

[no operator] implies OR

 

There are others, but the above are the most beneficial.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

 

for reference on fulltext mysql search

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

[DarkRanger]

Thanks guys. I got the search working exactly like it should. Really appreciate all the help!