Hello,
I have to build a keyword search with support for the boolean
operators +, -, * and ".
Basically it's quite easy: Connect the three tables with two
joins, keyword search with LIKE. But the boolean operators give
me a headache.
mysql> SELECT c.id FROM keywords AS a LEFT JOIN keywords_transfer AS b
-> ON a.id = b.keyword LEFT JOIN images AS c ON b.series = c.series
-> AND b.image = c.image WHERE a.word LIKE 'costa brava' AND a.word LIKE
-> 'Ziegenkäse' AND a.word NOT LIKE 'Dörfer';
Empty set (0.00 sec)
That was my first idea. It should return one row. It's clear that
it does not, because a.word contains only one keyword.
Second idea was to fetch all the images which are (not) matching
the query and calculate the intersection.
SELECT id, image, series, title, variant_t FROM images WHERE 1 OR
(image = '001' and series = '07001') OR (image = '002' and series
= '07001') OR (image = '003' and series = '07001') OR (image =
'004' and series = '07001') OR (image = '006' and series =
'07001') OR (image = '007' and series = '07001') OR (image =
'008' and series = '07001') OR (image = '009' and series =
'07001') OR (image = '010' and series = '07001') OR (image =
'011' and series = '07001') OR (image = '012' and series =
'07001') OR (image = '013' and series = '07001') OR (image =
'014' and series = '07001') OR (image = '015' and series =
'07001') OR (image = '016' and series = '07001') OR (image =
'017' and series = '07001') OR (image = '018' and series =
'07001') OR (image = '019' and series = '07001') OR (image =
'020' and series = '07001') OR (image = '021' and series = '07001')
If the search is only matching a few fields, it works quiet well,
but with a bigger result set it's very slow. Quering the table
using WHERE image IN (...) and series IN (...) is not possible
because it would fetch to much images if the keywords are
matching two different values for series.
Has anyone a hint for me to get that working at a good speed?
Regards,
A.
P.S: I have to use the fields series and image insted of the
primary keys to link the tables. If I don't do that, I have
problems with the data integriy because of the import
scripts.
--
mysql> SELECT * FROM keywords LIMIT 5;
+----+---------+--------+
| id | word | amount |
+----+---------+--------+
| 1 | Firence | 49 |
| 2 | Italien | 49 |
| 3 | Toscana | 49 |
| 4 | Florenz | 59 |
| 5 | ESF | 29 |
+----+---------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM keywords_transfer LIMIT 5;
+----+--------+-------+---------+
| id | series | image | keyword |
+----+--------+-------+---------+
| 1 | 00026 | 001 | 1 |
| 2 | 00026 | 001 | 2 |
| 3 | 00026 | 001 | 3 |
| 4 | 00026 | 001 | 4 |
| 5 | 00026 | 001 | 5 |
+----+--------+-------+---------+
5 rows in set (0.00 sec)
mysql> SELECT id, series, image FROM images LIMIT 5;
+----+--------+-------+
| id | series | image |
+----+--------+-------+
| 1 | 00026 | 001 |
| 2 | 00026 | 002 |
| 3 | 00026 | 003 |
| 4 | 00026 | 004 |
| 5 | 00026 | 005 |
+----+--------+-------+
5 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
