What search method should I use?

[vigour]

I have a table that contains 50000 rows.

I have 5 fields in my table that are called keyword1, keyword2, keyword3, keyword4, keyword5

The user wants to search for ever row that have the following 5 keywords (selected from a list with hundreds of keywords):

Example1:
Flower, House, Car, Chair, Dog

Example2:
Train, Flower, Cat, Dog, House

What search method should I use for this?

[Piranha]

You should not have 5 keywords in one table, it breaks the normalization rules. Instead you should use one table for keywords and another for the itemn and normally a table between them. Something like this:

table item
id
name
other columns

table keyword
id
name
other columns

table item_keyword
item_id foreign key
keyword_id foreign key

Search for database normalization to better understand this.

[vigour]

I just tried this method instead:

First I added a field called keywords to my table and indexed it as fulltext. I deleted the other 5 keyword fields.

Then I added 5 keywords in one of my rows like this 'Flower Dog Cat Car House'

I used this query:

$result = mysql_query("SELECT * FROM products WHERE MATCH(keywords) AGAINST('+Flower +Dog +Cat +Car +House' IN BOOLEAN MODE)");

The query gave me the right results so it seems that I'm doing it the right way. Can someone please give me feedback.

[vigour]

I just discovered that I get a hit if one or more of the 5 keyword matches in the following query.

$result = mysql_query("SELECT * FROM products WHERE MATCH(keywords) AGAINST('+Flower +Dog +Cat +Car +House' IN BOOLEAN MODE)");

How do I write the query so all 5 keyword must match before getting a hit? I know that I can use AND but there must be a better way...

[leatherback]

It works, but is not great.

Go with Piranha's solution, as it is the one which makes most sense:

<keywords>
[kw_id] [kw_label]

<items>
[it_id] [it_data]

<item_to_kw>
[it_nr] [kw_nr]

$sql = "
select * from items
LEFT JOIN item_to_kw
  ON it_id = it_nr
LEFT JOIN keywords
  on kw_nr = kw_id
WHERE kw_label = '".$keyword."'";

[vigour]

leatherback,

Will your code be able to search for 5 different keywords? As you can see I'm using full text search. What way is the fastest when dealing with lots of rows (50000)?

[leatherback]

Yes it will:

$sql = "
select * from items
LEFT JOIN item_to_kw
  ON it_id = it_nr
LEFT JOIN keywords
  on kw_nr = kw_id
WHERE (kw_label = '".$keyword1."') 
AND (kw_label = '".$keyword2."')
AND (kw_label = '".$keyword3."')
AND (kw_label = '".$keyword4."')
AND (kw_label = '".$keyword5."')"; 

this structure is ideal for larger databases, as searching through a list of words is much quicker then a full-text search. Also, this allows you to -in the future- decide you want more, or less, keywords stored.

[vigour]

leatherback,

I just added three tables like you and Piranha suggested but I get no hits when searching. Maybe you can show me data in all three tables, as an example? So I can learn how they work together. Maybe that's the problem I have.

[Piranha]

leatherback wrote:

Yes it will:

$sql = "
select * from items
LEFT JOIN item_to_kw
  ON it_id = it_nr
LEFT JOIN keywords
  on kw_nr = kw_id
WHERE (kw_label = '".$keyword1."') 
AND (kw_label = '".$keyword2."')
AND (kw_label = '".$keyword3."')
AND (kw_label = '".$keyword4."')
AND (kw_label = '".$keyword5."')"; 

That query won't work. You require that the kw_label column have all the keywords in each row, and that is simply not going to happen. I would do the query something like this:

$sql = "SELECT column
FROM item
WHERE id in (
	SELECT item_id
	FROM item_keyword
	LEFT JOIN keyword ON item_keyword.keyword_id = keyword.id
	WHERE keyword.key = '$key1'
	OR keyword.key = '$key2'
	OR keyword.key = '$key3'
	OR keyword.key = '$key4'
	OR keyword.key = '$key5'
	GROUP BY item_id
	HAVING COUNT(item_keyword.item_id) = 5
)"

[vigour]

Maybe I should explain what I want to do in a better way.

I have a item table with around 50000 rows. Each item can have up to 5 keywords that describe the item.

The user must be able to search for 5 keywords. And I only want hits where the item have all these 5 keywords, not just one of them.

When I tested leatherback's query I got no hits. But I had one 'test' item in the item table with 5 correct keywords at that point.

So something is not working like it should. Can someone please show me these three tables with data in them, just a couple of rows? It's easier for me that way.

My code looks like this:

// get user input
$keyword1 = $_POST["$keyword1"];
$keyword2 = $_POST["$keyword2"];
$keyword3 = $_POST["$keyword3"];
$keyword4 = $_POST["$keyword4"];
$keyword5 = $_POST["$keyword5"];

// connect to database
require("db_inc.php");

// search for items
$result = mysql_query("SELECT * FROM items LEFT JOIN item_to_kw ON it_id = it_nr LEFT JOIN keywords on kw_nr = kw_id WHERE (kw_label = '".$keyword1."') AND (kw_label = '".$keyword2."') AND (kw_label = '".$keyword3."') AND (kw_label = '".$keyword4."') AND (kw_label = '".$keyword5."')");

// present result
while($row=mysql_fetch_array($result))
	{
	echo $row['it_id'];
	echo '<br>';	
	}

// done		
echo 'Searching is done';

[Piranha]

Have you tried my query? It will only return where all 5 keywords match.

[vigour]

Piranha,

I'm just about to try it. Can you please rewrite your query with these names?

<keywords>
[kw_id] [kw_label]

<items>
[it_id] [it_data]

<item_to_kw>
[it_nr] [kw_nr]

[vigour]

I still get no hits from the database. My code looks like this:

$result = mysql_query("SELECT * FROM items WHERE it_id IN (SELECT it_nr FROM item_to_kw LEFT JOIN keywords ON item_to_kw.kw_nr = keywords.kw_id WHERE keywords.kw_label = '$keyword1' OR keywords.kw_label = '$keyword2' OR keywords.kw_label = '$keyword3' OR keywords.kw_label = '$keyword4' OR keywords.kw_label = '$keyword5' GROUP BY it_nr HAVING COUNT(item_to_kw.it_nr) = 5 )");

I have 2 rows that matches but no hits.

[Piranha]

If you tweak the subquery a little, what result do you get from this? Please run it in a admin program like PHPMyAdmin to see exactly what it returns. It will not be ordered, I just need to know if it returns some product with the count 5 or not.

SELECT it_nr, COUNT(item_to_kw.it_nr) FROM item_to_kw LEFT JOIN keywords ON item_to_kw.kw_nr = keywords.kw_id WHERE keywords.kw_label = '$keyword1' OR keywords.kw_label = '$keyword2' OR keywords.kw_label = '$keyword3' OR keywords.kw_label = '$keyword4' OR keywords.kw_label = '$keyword5' GROUP BY it_nr

Edit: If that does not return a bigger count than 1 then try the following query as well:

SELECT it_nr, COUNT(item_to_kw.kw_nr) FROM item_to_kw LEFT JOIN keywords ON item_to_kw.kw_nr = keywords.kw_id WHERE keywords.kw_label = '$keyword1' OR keywords.kw_label = '$keyword2' OR keywords.kw_label = '$keyword3' OR keywords.kw_label = '$keyword4' OR keywords.kw_label = '$keyword5' GROUP BY it_nr

The only difference is what variable they count on.

[leatherback]

Sorry, my bad. Here is a working solution

query:

SELECT p_title, count(p_id) as count
 FROM `pages` 
left join kw_t_p on p_id = p_nr
left join keyword on kw_id = kw_nr 
where (kw_label = 'test1') or
 (kw_label = 'test2') or
 (kw_label = 'test3') or
 (kw_label = 'test4') or
 (kw_label = 'test5') 
group by p_id
having count>=5

database:

-- 
-- Table structure for table `keyword`
-- 
-- Creation: Feb 20, 2007 at 12:34 PM
-- Last update: Feb 20, 2007 at 12:36 PM
-- 

CREATE TABLE `keyword` (
  `kw_id` smallint(6) NOT NULL auto_increment,
  `kw_label` text NOT NULL,
  PRIMARY KEY  (`kw_id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `keyword`
-- 

INSERT INTO `keyword` (`kw_id`, `kw_label`) VALUES (1, 'test1'),
(2, 'test2'),
(3, 'test3'),
(4, 'test4'),
(5, 'test5'),
(6, 'test6');

-- --------------------------------------------------------

-- 
-- Table structure for table `kw_t_p`
-- 
-- Creation: Feb 20, 2007 at 12:35 PM
-- Last update: Feb 20, 2007 at 12:37 PM
-- 

CREATE TABLE `kw_t_p` (
  `kw_nr` int(11) NOT NULL default '0',
  `p_nr` int(11) NOT NULL default '0'
) TYPE=MyISAM;

-- 
-- Dumping data for table `kw_t_p`
-- 

INSERT INTO `kw_t_p` (`kw_nr`, `p_nr`) VALUES (1, 1),
(1, 2),
(2, 1),
(3, 1),
(4, 1),
(5, 1);

-- --------------------------------------------------------

-- 
-- Table structure for table `pages`
-- 
-- Creation: Feb 20, 2007 at 12:35 PM
-- Last update: Feb 20, 2007 at 12:36 PM
-- 

CREATE TABLE `pages` (
  `p_id` int(11) NOT NULL auto_increment,
  `p_title` text NOT NULL,
  PRIMARY KEY  (`p_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `pages`
-- 

INSERT INTO `pages` (`p_id`, `p_title`) VALUES (1, 'a test page'),
(2, 'an other test page');

[vigour]

Piranha,

I used phpmyadmin for this test. Your first quary returned 5, I then changed one of the keywords so it did not match all 5 keywords and the quary returned 4.

Your second quary also returned 5.

[vigour]

I used phpmyadmin again. This quary returned an error saying, '#1064 - You have an error in your SQL syntax.'

SELECT * FROM items WHERE it_id IN (SELECT it_nr FROM item_to_kw LEFT JOIN keywords IN item_to_kw.kw_nr = keywords.kw_id WHERE keywords.kw_label = 'Blommor' OR keywords.kw_label = 'Gräsmatta' OR keywords.kw_label = 'Hus' OR keywords.kw_label = 'Stolar' OR keywords.kw_label = 'Träd' GROUP BY it_nr HAVING COUNT(item_to_kw.it_nr) = 5

[Piranha]

Du glömde den sista parantesen och skrev IN på ett ställe där det ska vara ON...

Translation: You forgot the last paranthesis and wrote IN in a place where it should be ON. It should be like this:

SELECT * 
FROM items 
WHERE it_id IN (
    SELECT it_nr 
    FROM item_to_kw 
    LEFT JOIN keywords ON item_to_kw.kw_nr = keywords.kw_id 
    WHERE keywords.kw_label = 'Blommor' 
    OR keywords.kw_label = 'Gräsmatta' 
    OR keywords.kw_label = 'Hus' 
    OR keywords.kw_label = 'Stolar' 
    OR keywords.kw_label = 'Träd' 
    GROUP BY it_nr 
    HAVING COUNT(item_to_kw.it_nr) = 5) 

[vigour]

Piranha,

That also gave me an error of type 1064 when I run it in phpmyadmin. This is the message I got.

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT it_nr FROM item_to_kw LEFT JOIN keywords ON item_to_kw .

Are you from Sweden? 😉

Piranha wrote:

Du glömde den sista parantesen och skrev IN på ett ställe där det ska vara ON...

Translation: You forgot the last paranthesis and wrote IN in a place where it should be ON. It should be like this:

SELECT * 
FROM items 
WHERE it_id IN (
    SELECT it_nr 
    FROM item_to_kw 
    LEFT JOIN keywords ON item_to_kw.kw_nr = keywords.kw_id 
    WHERE keywords.kw_label = 'Blommor' 
    OR keywords.kw_label = 'Gräsmatta' 
    OR keywords.kw_label = 'Hus' 
    OR keywords.kw_label = 'Stolar' 
    OR keywords.kw_label = 'Träd' 
    GROUP BY it_nr 
    HAVING COUNT(item_to_kw.it_nr) = 5) 

[Piranha]

How did you guess that I'm from Sweden? :p

What version of MySQL do you have? And have you tried leatherback's solution without a subquery?