MySQL fulltext or exact value search is very slow-CodePudding

I have this table of 9.5 million rows and I need to perform both fulltext and exact value search over the same column.

Altho there are 2 indexes over this column, one BTREE, one FULLTEXT, database engine doesn't use any and goes thru all 9.5M rows.

select * from mytable
where match(document) against (' 111/05257' in boolean mode)
   or document = '111/05257';
-- very slow, takes ~ 9 seconds
-- possible keys: both
-- used key: none :(

If I use only one type of search, queries run fast.

select * from mytable where document = '111/05257';
-- very fast, around 80 ms
-- used key: btree

select * from mytable where match(document) against (' 111/05257' in boolean mode)
-- very fast, around 100 ms
-- used key: fulltext

Given poorly structured data at document column, ranging from '1/XA' thru '5778292019' to 'S:NXA/0001/XA2019/111/05257', I need to use both exact and partial (fulltext) search over this column.

Wildcard searches ('1/05257%') also perform terribly over btree index.

Any idea how to solve this?

Thank you all

CodePudding user response：

Queries involving OR are notoriously hard to optimize. A common solution is to change them into two queries, and UNION the results:

select * from mytable
where match(document) against (' 111/05257' in boolean mode)
UNION
select * from mytable
where document = '111/05257';

Each of the respective queries should be free to use a different index. The UNION will eliminate any rows in common from the two results.