[5.x]: Missing index on the priority attribute in the 'queue' table, causing slow performance in job retrieval (15+ seconds).

[hatyi]

What happened?

Summary

This issue was reported to me by a colleague last week. We are working on a Craft Commerce website that requires heavy use of small, incremental processing. During certain processes, especially when external synchronizations are running overnight, we can end up with 10,000 to 30,000 queue jobs being created.

The 'queue' table in the database lacks an index on the priority attribute, which causes performance issues when retrieving the next job. Specifically, querying the next job was taking over 15 seconds because MySQL had to load and sort all results in memory based on CPU power. After adding an index on the priority column manually, the query time was reduced to around 0.005 seconds, which resolved the issue.

Details

• Problem: Missing index on the priority attribute in the 'queue' table, causing slow performance in job retrieval (15+ seconds).

• Impact: In a system with high volumes of queue jobs (10-30k jobs overnight), this slow performance significantly affects cron job processing and overall system efficiency.

• Solution: Manually added an index to the priority column, resulting in a drastic performance improvement (query now takes ~0.005 seconds).

• Observation: We have checked some other Craft based projects, and neither Craft 4 nor Craft 5 projects had this index, suggesting a potential design flaw in Craft.

Suggestion

Recommend adding an index to the priority column in the 'queue' table by default to improve performance in high-volume job-processing scenarios.

Craft CMS version

5.4.9

PHP version

8.2.25

Operating system and version

Debian 5.10.223-1 (2024-08-10) x86_64 GNU/Linux

Database type and version

MySQL 8.0.30

Image driver and version

No response

Installed plugins and versions

"algolia/algoliasearch-client-php": "^3.4",
"craftcms/ckeditor": "4.2.0",
"craftcms/cms": "5.4.9",
"craftcms/commerce": "5.1.1",
"mmikkel/incognito-field": "2.0.0",
"nystudio107/craft-vite": "5.0.1",
"presseddigital/linkit": "5.0.0",
"putyourlightson/craft-sendgrid": "3.0.0",
"setasign/fpdi": "^2.6",
"studioespresso/craft-scout": "5.0.1",
"tecnickcom/tcpdf": "^6.7",
"verbb/formie": "3.0.7",
"verbb/navigation": "3.0.4",
"verbb/wishlist": "3.0.3",
"vlucas/phpdotenv": "^5.4.0",
"yiisoft/yii2-redis": "^2.0"

[brandonkelly]

Just tested with 10,000 jobs in the queue and the query executed in 0.02 seconds.

Can you please send a backup of your queue table when it’s filled up with 10K+ rows?