How to use MATCH against LIKE in IF clause? (MySql)-CodePudding

I'd like to improve my query performance and use MATCH against LIKE.

My simplified strucure:

public:
- id (PRIMARY)
- title (FULLTEXT)
- ...

private
- id (PRIMARY)
- id_public (KEY)
- title (FULLTEXT)
- ...

I added fullindex like that:

ALTER TABLE public ADD FULLTEXT INDEX idx_title (title);
ALTER TABLE private ADD FULLTEXT INDEX idx_title (title);

Next I'd like to change my query but isn't simple.

In simple query I have changed query like that.

From

SELECT COUNT(*) FROM public WHERE `title` LIKE '%XYZ%';

SELECT COUNT(*) FROM public WHERE MATCH(title) AGAINST ('XYZ');

But I have query more specyfict with JOIN and IF. How can I use in this query MATCH?

SELECT COUNT(*) 
FROM public as public
LEFT JOIN private as private ON private.id_public = public.id
WHERE IF(private.title IS NULL, public.title, private.title) LIKE '%XYZ%'

I hope someone help me. I've spent a lot of time to find solution and nothing.

CodePudding user response：

If it's required that only one of the fields should be matched, then something like this should work

SELECT COUNT(*)
FROM public as public
LEFT JOIN private as private ON private.id_public = public.id
WHERE
  (private.title IS NOT NULL AND MATCH(private.title) AGAINST ('XYZ'))
  OR
  (private.title IS NULL AND MATCH(public.title) AGAINST ('XYZ'))