MySQL optimize OR with multiple tables

Question

i'm trying to find a better solution, because the actual query takes too long.

lets assume you have 3 tables: tasks, adresses, persons now we have a string like "tobias london" - we will split the string by spaces and get ["tobias","london"] (of course we could have a more complex string with more words)

now we want all tasks, that has matches in adresses OR persons AND ALL words must match

actual query example:

SELECT
  tasks.task_id
FROM
  (
    tasks,
    (SELECT
      adress_id AS id,
      'adress_id' AS typ
    FROM
      adresses
    WHERE adresses.city LIKE 'tobias%'
      OR adresses.street LIKE 'tobias%'
    UNION
    SELECT
      person_id AS id,
      'person_id' AS typ
    FROM
      persons
    WHERE persons.name LIKE 'tobias%') f0,
    (SELECT
      adress_id AS id,
      'adress_id' AS typ
    FROM
      adresses
    WHERE adresses.city LIKE 'london%'
      OR adresses.street LIKE 'london%'
    UNION
    SELECT
      person_id AS id,
      'person_id' AS typ
    FROM
      persons
    WHERE persons.name LIKE 'london%') f1
  )
WHERE (
    f0.typ = 'adress_id'
    AND tasks.adress_id = f0.id
    OR f0.typ = 'person_id'
    AND tasks.person_id = f0.id
  )
  AND (
    f1.typ = 'adress_id'
    AND tasks.adress_id = f1.id
    OR f1.typ = 'person_id'
    AND tasks.person_id = f1.id
  )

this is extremely slow, because of the OR within the WHERE-clauses (and this is just an example with 2 words)

anybody know how to make it better than this?

EDIT: as requested a small db-fiddle:

CREATE TABLE `adresses` (
  `adress_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `street` varchar(200) NOT NULL,
  `house_number` varchar(6) NOT NULL,
  `postal_code` smallint(5) unsigned NOT NULL,
  `city` varchar(100) NOT NULL,
  PRIMARY KEY (`adress_id`),
  KEY `street` (`street`),
  KEY `postal_code` (`postal_code`),
  KEY `city` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `adresses` */

insert  into `adresses`(`adress_id`,`street`,`house_number`,`postal_code`,`city`) values 
(1,'first','1',12345,'London'),
(2,'second','2',23456,'Paris'),
(3,'third','3',34567,'Okawa'),
(4,'fourth','4',45678,'Berlin'),
(5,'fiveth','5',56789,'ABCDE'),
(6,'sixth','6',65535,'DFGHJH');

/*Table structure for table `persons` */

CREATE TABLE `persons` (
  `person_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`person_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `persons` */

insert  into `persons`(`person_id`,`name`) values 
(1,'Anna'),
(2,'John'),
(3,'Michel'),
(4,'Olivia'),
(5,'Pavel'),
(6,'Peter'),
(7,'Sarah'),
(8,'Tobias');

/*Table structure for table `tasks` */

CREATE TABLE `tasks` (
  `task_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `person_id` int(10) unsigned NOT NULL,
  `adress_id` int(10) unsigned NOT NULL,
  `description` varchar(200) NOT NULL,
  PRIMARY KEY (`task_id`),
  KEY `person_id` (`person_id`),
  KEY `adress_id` (`adress_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `tasks` */

insert  into `tasks`(`task_id`,`person_id`,`adress_id`,`description`) values 
(1,1,2,''),
(2,2,3,''),
(3,3,4,''),
(4,5,6,''),
(5,4,1,''),
(6,1,4,''),
(7,3,3,''),
(8,8,1,'');


SELECT
  tasks.task_id
FROM
  (
    tasks,
    (SELECT
      adress_id AS id,
      'adress_id' AS typ
    FROM
      adresses
    WHERE adresses.city LIKE 'tobias%'
      OR adresses.street LIKE 'tobias%'
    UNION
    SELECT
      person_id AS id,
      'person_id' AS typ
    FROM
      persons
    WHERE persons.name LIKE 'tobias%') f0,
    (SELECT
      adress_id AS id,
      'adress_id' AS typ
    FROM
      adresses
    WHERE adresses.city LIKE 'london%'
      OR adresses.street LIKE 'london%'
    UNION
    SELECT
      person_id AS id,
      'person_id' AS typ
    FROM
      persons
    WHERE persons.name LIKE 'london%') f1
  )
WHERE (
    f0.typ = 'adress_id'
    AND tasks.adress_id = f0.id
    OR f0.typ = 'person_id'
    AND tasks.person_id = f0.id
  )
  AND (
    f1.typ = 'adress_id'
    AND tasks.adress_id = f1.id
    OR f1.typ = 'person_id'
    AND tasks.person_id = f1.id
  )

✓

✓

✓

✓

✓

✓

| task_id |
| ------: |
|       8 |

db<>fiddle here

Answer 1

Redesign the schema. The use of typ to pick between two table is a very bad idea in a Relational Database. (As you are finding out.)

After that, it may be useful to have a FULLTEXT index on the columns that could have "tobias%" or "london%". But the problem then comes because the address and person name are in different tables.

If you had both in the same table:

SELECT ...
    WHERE MATCH(firstname, last name, address, city)
          AGAINST("+tobias* +london*" IN BOOLEAN MODE)

And have this index on the table

FULLTEXT(firstname, lastname, address, city)

(Be aware of limitations of word length, etc.)

Another approach

Consider making a separate table (Search) with perhaps two columns:

• words - a mashup of all the things that users might search on (names, city, etc)
• task_id (or whatever is the necessary key) -- for joining to the other table(s)

Then use it (for example) thus:

SELECT ...
    FROM Search
    JOIN ... ON Search.task_id = ...
    WHERE MATCH(Search.text) AGAINST ("+word1 +word2" IN BOOLEAN MODE)
    ...

It helps to eliminate any 'short' words when building the query.

This technique takes extra effort when inserting rows. And there is redundant data in the database. (This is a no-no, but is often necessary for performance.)