Searching users extremely slow in large DB

[GaryReckard]

Description

I've been trying to determine the cause of some slow actions / queries, in particular, search for users on the users index page. I enter in a search term, and it takes 10+ seconds to respond.

Looking at the debug toolbar, I see the slowest query is the one that searches the searchindex, and looks like this:

SELECT * FROM `craft_searchindex` WHERE (`keywords` LIKE '%searchterms%') AND `siteId` = 4 AND `elementId` IN (... a list of almost 200k ids ...)

I ran that query directly on my DB and it took 8+ seconds to complete.

I also see a query executed right before that one that returns that same list of element ids. It looks like:

SELECT `elements`.`id`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_users` `users` ON `users`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_users` `users` ON `users`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`;

As a test, I put that query as a subquery in the first one, in place of the list of 200k element ids, to get:

SELECT * 
FROM 
	`craft_searchindex` 
WHERE 
	(`keywords` LIKE '%searchterms%') AND 
	`siteId` = 4 AND 
	`elementId` IN (
		SELECT `elements`.`id`
FROM (
	SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
	FROM `craft_elements` `elements`
	INNER JOIN `craft_users` `users` ON `users`.`id` = `elements`.`id`
	INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
	INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
	WHERE (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
) `subquery`
INNER JOIN `craft_users` `users` ON `users`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
	);

and that returned the same results as the first one, but in under 2 seconds.

I'm not sure if passing a set of IDs to the searchindex query is optimal for some range of numbers, but it appears to not be optimal in this case. Would love any other suggestions on how to get this to be more performant for my client. Thanks!

Additional info

• Craft version: 3.6.16
• PHP version: 7.3
• Database driver & version: MySQL 5.7.35

[angrybrad]

@GaryReckard any chance you're getting these results on AWS RDS?

[GaryReckard]

.... why yes... Why do you ask? 😐

[angrybrad]

@GaryReckard Trying to narrow some things down. Is this MySQL Aurora, or native MySQL? Serverless or "traditional"? Do you have a cluster setup at all?

[GaryReckard]

Currently on Mysql version 5.7.33-log. Not Aurura. No cluster. Currently on a db.m5.4xlarge instance (probably overprovisioned)

Here's a look at the performance profiling for the ajax call to search for users on the index page:

Really appreciate your time / help! Thanks!

[GaryReckard]

Also, in case this helps, here are the current indexes on

elements:

elements_sites

users

[angrybrad]

Hrm... can you run a SHOW VARIABLES; SQL query and share the results over at [email protected] and reference this ticket?

[GaryReckard]

Sent, thanks @angrybrad !

[brandonkelly]

Just released Craft 3.7.14 with the suggested subquery change. Thanks for the tip!

I’ve also added a bit of query caching to search queries and element index queries, which should help a bit as well. (3d799aeacfab047f41154d1c308f3015b0efdb8e)

[GaryReckard]

Heya @brandonkelly

Thanks for implementing that update!

I'm not sure if that type of update (passing a subquery into a query as opposed to a list of IDs generated from running that query) can be applied in other places, but I'm on Craft v3.7.33, and I'm still seeing queries in my debug profiler that are passed a giant list of element IDs.

Here's one query I'm seeing, as an example, that came from performing a search on an entries index page:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `users`.`username`, `users`.`firstName`, `users`.`lastName`, `users`.`email`, `users`.`unverifiedEmail`, `users`.`admin`, `users`.`locked`, `users`.`pending`, `users`.`suspended`, `users`.`lastLoginDate`, `users`.`lockoutDate`, `users`.`photoId`, `users`.`hasDashboard`, `content`.`id` AS `contentId`, `content`.`field_accountExpirationDate_rwnlunfx`, `content`.`field_accountValidUntil`, `content`.`field_active`, `content`.`field_addingSeats`, `content`.`field_addingTime`, `content`.`field_address`, `content`.`field_assetId`, `content`.`field_legacyAssociatedUserId`, `content`.`field_billingEmail`, `content`.`field_body`, `content`.`field_cidrIpRange`, `content`.`field_code`, `content`.`field_comment`, `content`.`field_couponCode_eufchsjv`, `content`.`field_customShippingMethod`, `content`.`field_customShippingMethodName`, `content`.`field_datePaused`, `content`.`field_dateRenewalProcessed`, `content`.`field_daysRemaining`, `content`.`field_daysToAdd_fedxbbqy`, `content`.`field_productDescription`, `content`.`field_disableEmails`, `content`.`field_displayCodeAsBoldText`, `content`.`field_displayContents`, `content`.`field_displayInPageNavigation`, `content`.`field_displayOrder`, `content`.`field_durationCredit`, `content`.`field_endDate`, `content`.`field_extendedTrial`, `content`.`field_faxNumber`, `content`.`field_freeTrialAccess`, `content`.`field_fullGroupNotificationThreshold`, `content`.`field_productFullTitle`, `content`.`field_groupName`, `content`.`field_hidePurchasingOnFrontEnd`, `content`.`field_peopleInOrganization`, `content`.`field_isPublic`, `content`.`field_isbnNumber`, `content`.`field_largeOrderThreshold`, `content`.`field_legacyId`, `content`.`field_userLegacyId`, `content`.`field_minimumNumberOfSeats`, `content`.`field_newsletter`, `content`.`field_openSeats`, `content`.`field_orderFirstName`, `content`.`field_keyOrderId`, `content`.`field_legacyOrderId`, `content`.`field_orderLastName`, `content`.`field_orderNotes`, `content`.`field_orderProducts`, `content`.`field_organization`, `content`.`field_page`, `content`.`field_pageIntro`, `content`.`field_phoneNumber`, `content`.`field_prepaidKeyHeading`, `content`.`field_prepaidKeyLink`, `content`.`field_processed`, `content`.`field_putNameOnItsOwnLine`, `content`.`field_redemptionDate`, `content`.`field_referenceNumber`, `content`.`field_relatedOrders`, `content`.`field_renewalReminderDate`, `content`.`field_selfOrder`, `content`.`field_shippingMessage`, `content`.`field_showTaxIdField`, `content`.`field_startDate`, `content`.`field_subscriptionCredits`, `content`.`field_subscriptionOwner_icrtwtiu`, `content`.`field_subscriptionStartDate`, `content`.`field_subscriptionYears`, `content`.`field_subtitle`, `content`.`field_taxId`, `content`.`field_teamSubscriptionRenewalWindow`, `content`.`field_customTitle`, `content`.`field_userTitle`, `content`.`field_totalPaidSeats`, `content`.`field_keyType`, `content`.`field_upgradeCredit_xfsnpuqg`, `content`.`field_upsAccountNumber`, `content`.`field_userType`, `content`.`field_vanityUrl`, `content`.`field_productVariantName`, `content`.`field_versionPreference`, `content`.`field_whitelistedDomains`, `elements`.`draftId`, `drafts`.`creatorId` AS `draftCreatorId`, `drafts`.`name` AS `draftName`, `drafts`.`notes` AS `draftNotes`, `drafts`.`provisional` AS `isProvisionalDraft`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_users` `users` ON `users`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`elements_sites`.`siteId`=4) AND (`elements`.`id` IN (

    --LOOOOONG LIST OF ELEMENT IDS--
   
)) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND ((`elements`.`draftId` IS NULL) OR (`drafts`.`provisional`=FALSE)) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_users` `users` ON `users`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`

Do you see that as a possible candidate for refactoring in the same way?

[brandonkelly]

@GaryReckard Any chance you can send in a database backup and your Composer files to [email protected]? We can look into it from there.

[GaryReckard]

Hey @brandonkelly

As this is a client's site with tons of user data, I don't think I could share a DB backup (I could ask the client for approval, possibly). I'll get back to you on that.

I did want to mention something I just noticed while testing. On the entries index page for a particular section, I was performing a search. When I searched for the term 'law', I got results in just a few seconds. When searching for a term like 'library' or 'circuit', or even just 'laws', it took 30 or 40+ seconds...

When I used the profiler, here is a query that took 42551.0 ms ms when searching for 'laws' for instance:

SELECT *
FROM `craft_searchindex`
WHERE (MATCH(`keywords`) AGAINST('+laws*' IN BOOLEAN MODE)) AND (`siteId`=4) AND (`elementId` IN (SELECT `elements`.`id`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`entries`.`sectionId` IN ('4', '3', '11', '8', '7', '13', '17', '5', '14', '15', '16', '10', '2', '18', '1')) AND (`entries`.`sectionId`='17') AND (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`canonicalId` IS NULL) AND ((`elements`.`draftId` IS NULL) OR (`drafts`.`provisional`=FALSE)) AND ((`elements`.`draftId` IS NULL) OR (NOT (`elements`.`canonicalId` IS NULL)) OR (`drafts`.`saved`=TRUE)) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`))

When I search for a term like law, this is the query executed, and in only 7.5 ms:

SELECT *
FROM `craft_searchindex`
WHERE ((`keywords` LIKE '% law%')) AND (`siteId`=4) AND (`elementId` IN (SELECT `elements`.`id`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`entries`.`sectionId` IN ('4', '3', '11', '8', '7', '13', '17', '5', '14', '15', '16', '10', '2', '18', '1')) AND (`entries`.`sectionId`='17') AND (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`canonicalId` IS NULL) AND ((`elements`.`draftId` IS NULL) OR (`drafts`.`provisional`=FALSE)) AND ((`elements`.`draftId` IS NULL) OR (NOT (`elements`.`canonicalId` IS NULL)) OR (`drafts`.`saved`=TRUE)) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`))

It's curious to me that two different search approaches are used: WHERE ((keywords LIKE '% law%')) and WHERE (MATCH(keywords) AGAINST('+laws*' IN BOOLEAN MODE)), and the former is SO much faster. Is the length of the keyword playing a role?

Really looking forward to getting this CP search much faster for our client... I recently migrated a Section with 1million+ entries from using Craft Entries to using a bespoke db table, which significantly reduced the size of some major tables

• craft_elements, from 2,509,914 down to 1,497,642 rows (down 40%)
• craft_content, from 2,044,733 down to 1,010,842 rows (down 51%)
• craft_elements_sites, from 2,474,765 down to 1,381,222 rows (down 44%)
• craft_entries, from 1,101,812 down to 105,350 rows (down 90%) !! and I was hoping that would show some noticeable improvements, but still having super slow searches...

Thanks for your help!

[angrybrad]

It's curious to me that two different search approaches are used: WHERE ((keywords LIKE '% law%')) and WHERE (MATCH(keywords) AGAINST('+laws*' IN BOOLEAN MODE)), and the former is SO much faster. Is the length of the keyword playing a role?

I can answer that part easily enough. MySQL has (by default) a minimum full-text search word length of 4: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ft_min_word_len

A way that Craft works around that for search terms under 4 characters is to fall back to a LIKE query. Definitely faster in most cases, but you lose a bunch of full-text search capabilities.

That's why you see a perf difference (and a change in the query) when searching for law vs. laws.

The rest of it is going to be extremely difficult to debug without some sort of access to the database and composer.json files. If you're able to reproduce these results locally, then that's probably the best-case scenario as we'd likely be able to replicate without access to the environment you're seeing it on, too.

[GaryReckard]

Hi @angrybrad Ah, that makes a lot of sense now. Thank you for that explanation!

I have reached out to our client about sharing the database, and will get back to you shortly.

In the meantime, I wanted to share the results of some tests I've done.

So, I've tried out these two queries:

Query 1

SELECT *
FROM `craft_searchindex`
WHERE ((`keywords` LIKE '% lawyer%')) AND (`siteId`=4) AND (`elementId` IN (SELECT `elements`.`id`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`entries`.`sectionId` IN ('4', '3', '11', '8', '7', '13', '17', '5', '14', '15', '16', '10', '2', '18', '1')) AND (`entries`.`sectionId`='17') AND (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`canonicalId` IS NULL) AND ((`elements`.`draftId` IS NULL) OR (`drafts`.`provisional`=FALSE)) AND ((`elements`.`draftId` IS NULL) OR (NOT (`elements`.`canonicalId` IS NULL)) OR (`drafts`.`saved`=TRUE)) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`));

Query 2

SELECT *
FROM `craft_searchindex`
WHERE (MATCH(`keywords`) AGAINST('+lawyer*' IN BOOLEAN MODE)) AND (`siteId`=4) AND (`elementId` IN (SELECT `elements`.`id`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`entries`.`sectionId` IN ('4', '3', '11', '8', '7', '13', '17', '5', '14', '15', '16', '10', '2', '18', '1')) AND (`entries`.`sectionId`='17') AND (`elements_sites`.`siteId`=4) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`canonicalId` IS NULL) AND ((`elements`.`draftId` IS NULL) OR (`drafts`.`provisional`=FALSE)) AND ((`elements`.`draftId` IS NULL) OR (NOT (`elements`.`canonicalId` IS NULL)) OR (`drafts`.`saved`=TRUE)) AND (`elements`.`revisionId` IS NULL)) `subquery`
INNER JOIN `craft_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`));

on several different database instances:

• Production: MySQL 5.7.33-log
• Local MariaDB: MariaDB 10.7.3 MySql 5.5.5
• Local MySQL: MySQL 5.7.37
• Staging: MySQL 5.7.31

Query Times

Production

• Query 1: 50ms ⚡️
• Query 2: 21.3 seconds 🐢🐢🐢

Local MariaDB

• Query 1: 25ms ⚡️
• Query 2: 550ms ⚡️

Local MySQL

• Query 1: 52ms ⚡️
• Query 2: ~5 seconds 🐢

Staging

• Query 1: 50ms ⚡️
• Query 2: ~1.5 seconds the first time, but ~50ms every time after...

Explain

I ran an EXPLAIN on Query 2 on each instance as well:

Production

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	craft_searchindex	NULL	fulltext	PRIMARY,craft_searchindex_keywords_idx	craft_searchindex_keywords_idx	0	const	1	100	Using where; Ft_hints: no_ranking
1	SIMPLE	entries	NULL	eq_ref	PRIMARY,craft_entries_sectionId_idx	PRIMARY	4	bluebook_prod.craft_searchindex.elementId	1	5	Using where
1	SIMPLE	entries	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.craft_searchindex.elementId	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY,craft_elements_dateDeleted_idx,craft_elements_archived_dateCreated_idx,craft_elements_draftId_fk,craft_elements_revisionId_fk,craft_fk_cuuwujycfdfpbpdiicmyjxbbbjstawmcgkgf,craft_idx_psvpjldazrlisbpzjsoarqpveqwnwqmzdykv,craft_idx_htbhxymidyyrciaabjyspgwumcktrgqgplmi	PRIMARY	4	bluebook_prod.craft_searchindex.elementId	1	6.25	Using where; Start temporary
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.elements.draftId	1	100	Using where
1	SIMPLE	content	NULL	eq_ref	PRIMARY,craft_content_elementId_siteId_unq_idx,craft_content_siteId_idx	craft_content_elementId_siteId_unq_idx	8	bluebook_prod.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	content	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.content.id	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY,craft_elements_sites_elementId_siteId_unq_idx,craft_elements_sites_siteId_idx	craft_elements_sites_elementId_siteId_unq_idx	8	bluebook_prod.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.elements_sites.id	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.craft_searchindex.elementId	1	100	NULL
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_prod.elements.draftId	1	100	Using index; End temporary

Local MariaDB

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	craft_searchindex	fulltext	PRIMARY,craft_searchindex_keywords_idx	craft_searchindex_keywords_idx	0		1	Using where
1	PRIMARY	entries	eq_ref	PRIMARY	PRIMARY	4	bluebook.craft_searchindex.elementId	1	Using index
1	PRIMARY	content	eq_ref	PRIMARY,craft_content_elementId_siteId_unq_idx,craft_content_siteId_idx	craft_content_elementId_siteId_unq_idx	8	bluebook.craft_searchindex.elementId,const	1	Using index
1	PRIMARY	content	eq_ref	PRIMARY	PRIMARY	4	bluebook.content.id	1	Using index
1	PRIMARY	elements_sites	eq_ref	PRIMARY,craft_elements_sites_elementId_siteId_unq_idx,craft_elements_sites_siteId_idx	craft_elements_sites_elementId_siteId_unq_idx	8	bluebook.craft_searchindex.elementId,const	1	Using index
1	PRIMARY	elements_sites	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements_sites.id	1	Using index
1	PRIMARY	entries	eq_ref	PRIMARY,craft_entries_sectionId_idx	PRIMARY	4	bluebook.craft_searchindex.elementId	1	Using where
1	PRIMARY	elements	eq_ref	PRIMARY,craft_elements_dateDeleted_idx,craft_elements_archived_dateCreated_idx,craft_elements_draftId_fk,craft_elements_revisionId_fk,craft_fk_cuuwujycfdfpbpdiicmyjxbbbjstawmcgkgf,craft_idx_psvpjldazrlisbpzjsoarqpveqwnwqmzdykv,craft_idx_htbhxymidyyrciaabjyspgwumcktrgqgplmi	PRIMARY	4	bluebook.craft_searchindex.elementId	1	Using where; Start temporary
1	PRIMARY	drafts	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements.draftId	1	Using where
1	PRIMARY	elements	eq_ref	PRIMARY	PRIMARY	4	bluebook.craft_searchindex.elementId	1
1	PRIMARY	drafts	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements.draftId	1	Using where; Using index; End temporary

Local MySQL

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	craft_searchindex	NULL	fulltext	PRIMARY,craft_searchindex_keywords_idx	craft_searchindex_keywords_idx	0	const	1	100	Using where; Ft_hints: no_ranking
1	SIMPLE	entries	NULL	eq_ref	PRIMARY,craft_entries_sectionId_idx	PRIMARY	4	bluebook.craft_searchindex.elementId	1	5	Using where
1	SIMPLE	entries	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.craft_searchindex.elementId	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY,craft_elements_dateDeleted_idx,craft_elements_archived_dateCreated_idx,craft_elements_draftId_fk,craft_elements_revisionId_fk,craft_fk_cuuwujycfdfpbpdiicmyjxbbbjstawmcgkgf,craft_idx_psvpjldazrlisbpzjsoarqpveqwnwqmzdykv,craft_idx_htbhxymidyyrciaabjyspgwumcktrgqgplmi	PRIMARY	4	bluebook.craft_searchindex.elementId	1	6.25	Using where; Start temporary
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements.draftId	1	100	Using where
1	SIMPLE	content	NULL	eq_ref	PRIMARY,craft_content_elementId_siteId_unq_idx,craft_content_siteId_idx	craft_content_elementId_siteId_unq_idx	8	bluebook.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	content	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.content.id	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.craft_searchindex.elementId	1	100	NULL
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements.draftId	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY,craft_elements_sites_elementId_siteId_unq_idx,craft_elements_sites_siteId_idx	craft_elements_sites_elementId_siteId_unq_idx	8	bluebook.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook.elements_sites.id	1	100	Using index; End temporary

Staging

1	SIMPLE	craft_searchindex	NULL	fulltext	PRIMARY,craft_searchindex_keywords_idx	craft_searchindex_keywords_idx	0	const	1	100	Using where; Ft_hints: no_ranking
1	SIMPLE	entries	NULL	eq_ref	PRIMARY,craft_entries_sectionId_idx	PRIMARY	4	bluebook_staging.craft_searchindex.elementId	1	5	Using where
1	SIMPLE	entries	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.craft_searchindex.elementId	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY,craft_elements_dateDeleted_idx,craft_elements_archived_dateCreated_idx,craft_elements_draftId_fk,craft_elements_revisionId_fk,craft_fk_cuuwujycfdfpbpdiicmyjxbbbjstawmcgkgf,craft_idx_psvpjldazrlisbpzjsoarqpveqwnwqmzdykv,craft_idx_htbhxymidyyrciaabjyspgwumcktrgqgplmi	PRIMARY	4	bluebook_staging.craft_searchindex.elementId	1	6.25	Using where; Start temporary
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.elements.draftId	1	100	Using where
1	SIMPLE	content	NULL	eq_ref	PRIMARY,craft_content_elementId_siteId_unq_idx,craft_content_siteId_idx	craft_content_elementId_siteId_unq_idx	8	bluebook_staging.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	content	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.content.id	1	100	Using index
1	SIMPLE	elements	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.craft_searchindex.elementId	1	100	NULL
1	SIMPLE	drafts	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.elements.draftId	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY,craft_elements_sites_elementId_siteId_unq_idx,craft_elements_sites_siteId_idx	craft_elements_sites_elementId_siteId_unq_idx	8	bluebook_staging.craft_searchindex.elementId,const	1	100	Using index
1	SIMPLE	elements_sites	NULL	eq_ref	PRIMARY	PRIMARY	4	bluebook_staging.elements_sites.id	1	100	Using index; End temporary

Hope that is helpful! I'll reply back about the database back once I hear back. Let me know if there are any other details that could be helpful. Thanks again, have a great day/weekend!

[angrybrad]

So the takeaway from your benchmarks is:

• LIKE queries are consistently fast across all environments
• Full-text queries can vary anywhere from 50ms to 21 seconds across environments

I suppose the next thing I'd do is dump the output from SHOW VARIABLES across all of the environments to see if a difference in configuration is causing full text query performance to vary across enviornments.

[GaryReckard]

Hi @angrybrad

I think I got this one licked, finally! And I found two different solutions, both rather simple.

From your suggestions, I decided to spin up a new database instance, migrating from a snapshot of our MySQL db to a new MariaDB instance. Once this was up and running, I ran some test queries on it, both the 'LIKE' type search, and the fulltext 'MATCH ... AGAINST...' type queries. The 'LIKE' queries performed snappy, returning results in 50 to 100ms. But the fulltext searches were dismal. The first execution in one test took 130 seconds, and subsequent executions with the same keyword took 8.5 seconds. For another keyword, the first execution took 227 seconds, and subsequent executions took 8.5 seconds.

So this isn't what I expected, but seemed to me to point to something amiss with the underlying storage of the data and/or indexes.

Dismayed, I started poking around to see if there was a way to force Craft to not use the fulltext type searches. I was considering putting in a feature request for a config setting, when I found this thread, describing a similar situation, and the useFullText setting.

Hey Romain,

Just added a new $useFullText property to craft\services\Search, which can be set to false from config/app.php to completely disable fulltext searching.

return [
    'components' => [
        'search' => [
            'useFullText' => false,
        ],
    ],
];

Turns out, that one setting, once I deployed it to production, got our CMS searches to be snappy again! REJOICE! So that is solution 1.

As a test, I decided to export the entire DB on my new MariaDB instance, and then re-import it, figuring that would give it a fresh start, and potentially get rid of any weirdness that existed with the current data on disk. Lo and behold, that ALSO solved the problem. On that MariaDB instance, the fulltext searches are finishing in between 50 and 150 miliseconds. So, that's solution 2.

I'm planning on scheduling a maintenance window sometime soon, and performing a full export and re-import of the production DB, as I can only see that helping performance.

Still not sure what caused our DB to get into that crappy state, but will keep an eye on it. Hope this thread proves useful for anyone else having similar issues.

Thanks again for your help in debugging this!

[angrybrad]

@GaryReckard

Solution 1: I completely forgot we added that property! You'll obviously lose any of the full-text search benefits.

Solution 2: Interesting - I wonder if you'd get the same performance benefits without the need for export/re-importing by running MySQL's OPTIMIZE. Maybe something like mysqlcheck -u user -p pwd -o --all-databases which should run optimize on all tables and databases.

[brandonkelly]

We’ve made some search performance improvements for Craft 4.6 (#14055). Seeing a 5-50% improvement on search queries ordered by score (depending on the number of matched elements), and search queries that aren’t ordered by score are now indistinguishable from normal element queries.

[brandonkelly]

Craft 4.6.0 is out with those improvements 🎉