I am trying to speed up keyword searches using MATCH AGAINST instead of
like, but am getting unexpected results.
I had assumed that using 'WHERE MATCH fulltext(column.name) AGAINST
'searchterm' would produce the same results as 'WHERE column.name LIKE
'%searchterm%' but it doesn't seem to.
Actual queries:
First one returns 190 rows using:
SELECT affiliates.id,
affiliates.site_name,affiliates.site_desc,affiliates.company FROM affiliates
WHERE MATCH (affiliates.site_desc,affiliates.site_name,affiliates.company)
AGAINST ('shop');
Second one returns 1168 rows using:
SELECT affiliates.id,
affiliates.site_name,affiliates.site_desc,affiliates.company FROM affiliates
WHERE affiliates.site_name like '%shop%' OR affiliates.site_desc like
'%shop%' OR affiliates.company like '%shop%';
This is the index:
mysql> show index from affiliates;
+------------+------------+------------+--------------+-------------+-------
----+-------------+----------+--------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+------------+--------------+-------------+-------
----+-------------+----------+--------+----------+
| affiliates | 0 | PRIMARY | 1 | id | A
| 9019 | NULL | NULL | |
| affiliates | 0 | id | 1 | id | A
| 9019 | NULL | NULL | |
| affiliates | 1 | SEARCHTEXT | 1 | site_desc | A
| NULL | 1 | NULL | FULLTEXT |
| affiliates | 1 | SEARCHTEXT | 2 | site_name | A
| NULL | NULL | NULL | FULLTEXT |
| affiliates | 1 | SEARCHTEXT | 3 | company | A
| NULL | NULL | NULL | FULLTEXT |
+------------+------------+------------+--------------+-------------+-------
----+-------------+----------+--------+----------+
5 rows in set (0.00 sec)
This is the table:
mysql> show columns from affiliates;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(20) | | PRI | NULL | auto_increment |
| contact_name | varchar(100) | | | | |
| company | varchar(75) | YES | | NULL | |
| address_1 | varchar(100) | YES | | NULL | |
| address_2 | varchar(100) | YES | | NULL | |
| city | varchar(25) | YES | | NULL | |
| county | varchar(25) | YES | | NULL | |
| post_code | varchar(12) | YES | | NULL | |
| country | varchar(25) | YES | | NULL | |
| email | varchar(75) | | | | |
| telephone | varchar(20) | YES | | NULL | |
| cheques | varchar(75) | YES | | NULL | |
| vat_reg | char(1) | YES | | 0 | |
| vat_type | char(1) | YES | | NULL | |
| vat_no | varchar(13) | YES | | NULL | |
| site_name | varchar(75) | | | | |
| URL | varchar(150) | YES | | NULL | |
| site_desc | text | YES | MUL | NULL | |
| primary_content | varchar(20) | YES | | NULL | |
| secondary_content | varchar(20) | YES | | NULL | |
| password | varchar(20) | | | | |
| refered | int(11) | YES | | NULL | |
| timestamp | datetime | YES | | NULL | |
| confirmed | tinyint(1) | YES | | 0 | |
| active | tinyint(1) | YES | | 1 | |
| confirmcode | varchar(25) | YES | | NULL | |
| signup_ip | varchar(15) | YES | | NULL | |
| email_list | tinyint(1) | YES | | 1 | |
+-------------------+--------------+------+-----+---------+----------------+
28 rows in set (0.00 sec)
Any thoughts please?
---------------------------
Peter Dunham
www.affiliatewindow.com
the future of web marketing
---------------------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
