An asterisk search operator inside quotations

[busboy]

I was hoping this part of my query would return rows with viral infection and viral infections. This isn't possible?

MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)


Thanks

[K. Wolfe]

Depending on what server you are querying:

select
*
from
table_name
where
column_name like '%viral infection%'

[r937]

there's a problem with LIKE and a leading wildcard, however...

[K. Wolfe]

there's a problem with LIKE and a leading wildcard, however...

I'm not able to test at the moment, is this because it will be expecting a character to be present?

[r937]

no, the problem is that LIKE with a leading wildcard cannot use a normal index (if one exists), and requires a table scan

whereas the fulltext index search should be close to instantaneous

the LIKE with a leading wildcard works the same as if i asked you to go to your white pages phone book and pull out all the last names with "sto" somewhere in them -- Astor, Caston, Johnstone, Winston -- so you have to read the entire book sequentially to find them all

[K. Wolfe]

Ah, performance wise absolutely. I suppose I could / should have brought that up.

[busboy]

So with my query above, is there any way to return both viral infection as well as viral infections?

Thank you.

[K. Wolfe]

So with my query above, is there any way to return both viral infection as well as viral infections?

Thank you.

where column_name in ('viral infection', 'viral infections')

where (column_name = 'viral infection' or column_name = 'viral infections')

[busboy]

Yes, those work too. But the goal is to keep it in this boolean format:

MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)

Because sometimes I throw lots of other keywords in there like:

"viral infection", oregano, "immune system", ill*, wart*

[K. Wolfe]

Yes, those work too. But the goal is to keep it in this boolean format:

MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)

Because sometimes I throw lots of other keywords in there like:

"viral infection", oregano, "immune system", ill*, wart*

What does boolean format have to do with anything. If you want an absolute flexible query then use my first suggestion, though it will be slower unless you do not need the wildcard at the beginning of the string.

[guelphdad]

What does boolean format have to do with anything.

It has to do with Boolean FULL TEXT searches:
http://dev.mysql.com/doc/refman/5.5/...t-boolean.html

Busboy, I believe your query should return both terms you are looking for the way it is now.

[K. Wolfe]

It has to do with Boolean FULL TEXT searches:
http://dev.mysql.com/doc/refman/5.5/...t-boolean.html

Busboy, I believe your query should return both terms you are looking for the way it is now.

It has nothing to do with what he is trying to accomplish.