Take the 2-minute tour ×

I am creating a search using MySQL & PHP on an existing table structure.

Multiple search keywords can be entered and the user can opt to either match ALL or ANY. The any form is not too difficult, but i am breaking my head on writing an efficient solution for the AND form.

The following is about the AND form, so all the search keywords must be found.

The 2 tables i have to work with (search in) have a structure as follows:

Table1 - item_id (non-unique) - text

Table2 - item_id (unique) - text_a - text_b - text_c

(The real solution will also have a 3rd table, but that is structure the same way as Table1. Table2 will have around 20 searchable columns)

Table1 can have multiple rows for each item_id with different text.

Consider having only 2 search keywords (can be more in real live), then both must exist in: - both in a single row/column or: - in 2 different columns of maybe different tables. or: - in 2 different rows with the same item_id (in case of both keywords found in different rows of Table1)

All i could come up with are very intensive sub-queries but that would bring the server down or the response times would be huge.

As i am using PHP i could use intermediate queries and store the results for use in a later final query.

Anyone some good suggestions?

Edit: There where requests for real examples, so here it goes.

Consider the following 2 tables with data:
Table 1

| item_id | t1_text_a | t1_text_b | t1_text_c | t1_text_d |
|       1 | aaa bbb   | NULL      | ccc       | ddd       |
|       2 | aaa ccc   | ddd       | fff       | ggg       |
|       3 | bbb       | NULL      | NULL      | NULL      |


| item_id | sequence | t2_text |
|       1 |        1 | kkk lll |
|       2 |        1 | kkk     |
|       2 |        2 | lll     |
|       3 |        1 | mmm     |

PS In the real database (which i can not change, so full text indexes or changes to table definition are not an option) Table1 has about 20 searchable columns and there are 2 tables like Table2. This should not make a difference to the solution, although it is something to consider from a performance perspective.

Example searches:
Keywords: aaa bbb
Should return:
- item_id=1. Both keywords are found in column t1_text_a.

Keywords: ccc ddd
Should return:
- item_id=1. "ccc" is found in t1_text_c, "ddd" is found in t1_text_d.
- item_id=2. "ccc" is found in t1_text_a, "ddd" is found in t1_text_b.

Keywords: kkk lll
Should return:
- item_id=1. Both keywords found in a single row of Table2 in column t2_text.
- item_id=2. Both keywords found in Table2, but in separate rows with the same item_id.

Keywords: bbb mmm
Should return:
- item_id=3. "bbb" is found in table1.t1_text_a, "mmm" is found in table2.t2_text.

My progress so far

I actually, for now, gave up on trying to catch this in mostly SQL.

What i did do is to create a query for each table retrieving any row that matches at least 1 of the search keywords. If there is only 1 search keyword the query uses a LIKE, otherwise a REGEXP 'keyword1|keyword2'.

These rows are put in a PHP array with the item_id as the index, and a concatenation of all the strings (searchable columns) as value. When finished retrieving all possible rows, i search the array for rows that match all keywords in the concatenated field.

Most likely not the best solution and it will not scale very well if the search will return many candidate rows with at least 1 match.

share|improve this question
Don't be shy... show what you've tried –  Tom H. Oct 19 '11 at 15:58
Nobody got an idea? –  Marco Oct 24 '11 at 4:22
add comment

1 Answer

It's hard to provide you with a finite answer since you do not give a lot of details about your case. But maybe this can give you a starting point:

SELECT * FROM table1 AS tbl1
INNER JOIN table2 AS tbl2
tbl1.text LIKE %search_word1%
AND tbl1.text LIKE %search_word2% 
AND tbl2.text_a LIKE %search_word1% 
AND tbl2.text_a LIKE %search_word2% 
AND tbl2.text_b LIKE %search_word1% 
AND tbl2.text_b LIKE %search_word2% 
AND tbl2.text_c LIKE %search_word1% 
AND tbl2.text_c LIKE %search_word2% 

You can adapt with JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN and the different LIKE and AND/OR statements to obtain the result you're looking for. Google some join examples with LIKE statements for more details.

But as Tom H. said, it'd be better if you could post a more precise table structure and a real exemple of search terms...

share|improve this answer
Thanks for trying to answer my question, but this doesn't work. If it was only this simple... Your query will only return rows where all search keywords are found in all columns. The search should return values where all keywords are found in any column or row. I thought that my original post contained enough information, but will try to create a real example test case and update the question. –  Marco Oct 20 '11 at 2:52
First post updated with better examples. –  Marco Oct 20 '11 at 3:49
add comment

Your Answer


