mysql - Performance of fulltext search in joined tables -

i have 3 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 organisations, while dp_users contains users. dp_organisation_member relationship between users , organisations. each user member of @ 1 organisation.

now search users matching string. want check both user's information , user's organisation's information when doing search, fulltext indexes on both dp_users , dp_organisation should used. created following query achieve this:

select * dp_user u left join dp_organisation_member m on m.`userid` = u.`userid` left join dp_organisation o on o.`organisationid` = m.`organisationid` match(u.`alias`, u.`firstname`, u.`surname`, u.`email`) against ('foo') or match(o.`name`, o.`shortname`) against ('foo') 

but query performs bad. test, tried following, searches in user's information:

select * dp_user u left join dp_organisation_member m on m.`userid` = u.`userid` left join dp_organisation o on o.`organisationid` = m.`organisationid` match(u.`alias`, u.`firstname`, u.`surname`, u.`email`) against ('foo') 

it runs around 30 times faster.

if search in organisation's information:

select * dp_user u left join dp_organisation_member m on m.`userid` = u.`userid` left join dp_organisation o on o.`organisationid` = m.`organisationid` match(o.`name`, o.`shortname`) against ('foo') 

the query slow again.

to check there nothing wrong fulltext index in dp_organisation, reversed queries select dp_organisation , join dp_user:

select * dp_organisation o left join dp_organisation_member m on m.`organisationid` = o.`organisationid` left join dp_user u on u.`userid` = m.`userid` match(u.`alias`, u.`firstname`, u.`surname`, u.`email`) against ('foo') or match(o.`name`, o.`shortname`) against ('foo') 

the above query slow, , 1 searching in user's information:

select * dp_organisation o left join dp_organisation_member m on m.`organisationid` = o.`organisationid` left join dp_user u on u.`userid` = m.`userid` match(u.`alias`, u.`firstname`, u.`surname`, u.`email`) against ('foo') 

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

select * dp_organisation o left join dp_organisation_member m on m.`organisationid` = o.`organisationid` left join dp_user u on u.`userid` = m.`userid` match(o.`name`, o.`shortname`) against ('foo') 

so seems performance when doing fulltext search in main table, , not ones joined table. can performance when doing fulltext search in joined table?

combining fts , join in queries results in slow down because mysql uses 1 index per table. when perform fts on table mysql uses full text index on table, it's not possible use index join.

in other news, indexes on dp_organisation_member table doesn't make lot of sense. have made user_id field unique. means user can belong 1 organization, means dp_organisation_member table redundant. have on normalized. can drop table , add organization id dp_user , eliminate 1 of joins.