So, I have a situation where I want both no MATCH and MATCH all items to get me the same results, but have been getting inconsistent results.
These are the current types in the DB:
mysql> select the_type from data GROUP BY the_type;
+-----------------+
| the_type |
+-----------------+
| type1 |
| type2 |
+-----------------+
If I don't specify any criteria:
mysql> select COUNT(*) as num from data;
+------+
| num |
+------+
| 2131 |
+------+
With a MATCH for all types:
mysql> select COUNT(*) as num from data WHERE MATCH('(@the_type type1|type2)');
+------+
| num |
+------+
| 430 |
+------+
Is it me or should both queries return the same exact count?
Edit:
After some digging I found out that one of my "types" had hyphen in it.
So something like:
MATCH('(@the_type type1|type2)')
will result in "type1" OR "type2", but something like:
MATCH('(@the_type type1|type-two)')
will be "type1" OR "type" AND "two".
Now I tried converting the type to an INT field and was trying to do:
mysql> select COUNT(*) as num from data WHERE type = 1 OR type = 2;
but I get the error:
ERROR 1064 (42000): sphinxql: syntax error, unexpected OR, expecting $end near 'OR type = 2'
Any ideas?
There is no 'OR' in sphinx'es WHERE clause.
But can do WHERE type IN (1,2);
If really want to use OR, can put it in the SELECT part, (see also the IF function)
select COUNT(*) as num, (type = 1 OR type = 2) AS filter from data WHERE filter =1;
could also try MATCH('(@the_type type1|"type-two")')
To keep the hyphenated (or multiple word) version together.