CODEWITHSUNDEEP
mysqlperformancejoinfull-text-search
Magnar Myrtveit
Magnar Myrtveit

Reputation: 2740

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?

Upvotes: 2

Views: 313

Answers (2)

Rick James
Rick James

Reputation: 142208

I recommend switching to InnoDB for starters. As of 5.6.4, FULLTEXT was available with it. There are a few differences to be aware of.

When the Optimizer has a choice between a MATCH and some other type of filter, it will do the FULLTEXT, not the other.

WHERE MATCH... OR MATCH...is bad because of the OR. And FULTEXT does poorly here. Turning it into ( SELECT ... MATCH ) UNION ( SELECT ... MATCH ) is a possible workaround.

LEFT JOIN likes to filter on the 'left' table first. So it is ok for that table to use FULLTEXT but not the 'right' table. In general, do not use LEFT unless you need it.

Upvotes: 0

e4c5
e4c5

Reputation: 53734

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.

Upvotes: 1

Related Questions