Magnar Myrtveit Magnar Myrtveit - 21 days ago 8
MySQL Question

Performance of fulltext search in joined tables

I have three tables:

CREATE TABLE `dp_organisation` (
`OrganisationId` bigint(32) NOT NULL AUTO_INCREMENT,
`Name` text COLLATE utf8mb4_unicode_ci NOT NULL,
`ShortName` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`OrganisationId`),
FULLTEXT KEY `fulltext` (`Name`,`ShortName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `dp_organisation_member` (
`OrganisationId` bigint(32) NOT NULL,
`UserId` bigint(32) NOT NULL,
PRIMARY KEY (`OrganisationId`,`UserId`),
UNIQUE KEY `UserId` (`UserId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `dp_user` (
`UserId` bigint(32) NOT NULL AUTO_INCREMENT,
`Alias` varchar(125) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Firstname` text COLLATE utf8mb4_unicode_ci NOT NULL,
`Surname` text COLLATE utf8mb4_unicode_ci,
`Email` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`UserId`),
FULLTEXT KEY `fulltext` (`Alias`,`Firstname`,`Surname`,`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


dp_organisation
contains all organisations, while
dp_users
contains all users.
dp_organisation_member
is the relationship between users and organisations. Each user is a member of at most one organisation.

Now I would like to search for users matching some string. I want to check both the user's information and the user's organisation's information when doing the search, so the fulltext indexes on both
dp_users
and
dp_organisation
should be used. I created the following query to achieve this:

SELECT *
FROM dp_user u
LEFT JOIN dp_organisation_member m ON m.`UserId` = u.`UserId`
LEFT JOIN dp_organisation o ON o.`OrganisationId` = m.`OrganisationId`
WHERE MATCH(u.`Alias`, u.`Firstname`, u.`Surname`, u.`Email`) AGAINST ('foo')
OR MATCH(o.`Name`, o.`ShortName`) AGAINST ('foo')


But the query performs really bad. Just to test, I tried the following, which only searches in the user's information:

SELECT *
FROM dp_user u
LEFT JOIN dp_organisation_member m ON m.`UserId` = u.`UserId`
LEFT JOIN dp_organisation o ON o.`OrganisationId` = m.`OrganisationId`
WHERE MATCH(u.`Alias`, u.`Firstname`, u.`Surname`, u.`Email`) AGAINST ('foo')


It runs around 30 times faster.

If I search only in the organisation's information:

SELECT *
FROM dp_user u
LEFT JOIN dp_organisation_member m ON m.`UserId` = u.`UserId`
LEFT JOIN dp_organisation o ON o.`OrganisationId` = m.`OrganisationId`
WHERE MATCH(o.`Name`, o.`ShortName`) AGAINST ('foo')


The query is slow again.

To check that there is nothing wrong with the fulltext index in
dp_organisation
, I reversed the queries to select from
dp_organisation
and join
dp_user
:

SELECT *
FROM dp_organisation o
LEFT JOIN dp_organisation_member m ON m.`OrganisationId` = o.`OrganisationId`
LEFT JOIN dp_user u ON u.`UserId` = m.`UserId`
WHERE MATCH(u.`Alias`, u.`Firstname`, u.`Surname`, u.`Email`) AGAINST ('foo')
OR MATCH(o.`Name`, o.`ShortName`) AGAINST ('foo')


The above query is slow, and so is the one searching only in the user's information:

SELECT *
FROM dp_organisation o
LEFT JOIN dp_organisation_member m ON m.`OrganisationId` = o.`OrganisationId`
LEFT JOIN dp_user u ON u.`UserId` = m.`UserId`
WHERE MATCH(u.`Alias`, u.`Firstname`, u.`Surname`, u.`Email`) AGAINST ('foo')


The query searching only in the organisation's information, however, is fast (around 25 times faster):

SELECT *
FROM dp_organisation o
LEFT JOIN dp_organisation_member m ON m.`OrganisationId` = o.`OrganisationId`
LEFT JOIN dp_user u ON u.`UserId` = m.`UserId`
WHERE MATCH(o.`Name`, o.`ShortName`) AGAINST ('foo')


So it seems that I only get good performance when doing the fulltext search in the main table, and not the ones that are joined into that table. What can I do to get good performance when doing a fulltext search in a joined table?

Answer

Combining FTS and JOIN in your queries results in a slow down because mysql generally uses only one index per table. When you perform a FTS on a table mysql uses the full text index on that table, thus it's not possible to use an index for the join.

In other news, the indexes on the dp_organisation_member table doesn't make a lot of sense. You have made the user_id field unique. That means a user can belong to only one organization, which actually means the dp_organisation_member table is redundant. You have over normalized. You can drop this table and add an organization id to dp_user and eliminate one of your joins.

Comments