0

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?

mumia
  • 83
  • 1
  • 10

1 Answers1

1

There is no 'OR' in sphinx'es WHERE clause.

  1. But can do WHERE type IN (1,2);

  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;

  3. could also try MATCH('(@the_type type1|"type-two")') To keep the hyphenated (or multiple word) version together.

barryhunter
  • 20,886
  • 3
  • 30
  • 43