First off: I am not a DB expert.
Queries below are performed in a large table with around 130.000.000 entries.
I am using Mariadb 10.6.16
There are multiple issues reported on ROWID filter performance degradation, I am not sure if this matches an existing/open issue or not.
From time to time a SELECT query takes minutes where I would expect it to take milliseconds, I noticed that in every occurrence, the amount of entries returned (or matching the query) was very small.
The problematic query is as follows: (Hibernate generated, reduced to make it readable)
SELECT *
|
FROM
|
`payment_transaction` pt1_0 where pt1_0.`merchant_id`='<some id>' AND
|
pt1_0.`device_id`='<some id>'
|
order by pt1_0.`timestamp` desc
|
There are both indices starting with MERCHANT_ID and DEVICE_ID, using either of these indices would limit the result to a mere 55 entries in 1 case. However, the query takes 3-4 minutes to complete.
I ran the following :
SET SESSION optimizer_switch='rowid_filter=on';
|
|
DESCRIBE FORMAT=JSON
|
SELECT SQL_NO_CACHE *
|
FROM
|
`payment_transaction` pt1_0 where pt1_0.`merchant_id`='<some id>' and 1=1 AND
|
pt1_0.`device_id`='<some other id>'
|
order by pt1_0.`timestamp` desc
|
This yields:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "pt1_0",
|
"access_type": "ref",
|
"possible_keys": [
|
"idx_merchant_transactions",
|
"idx_device_id_shoplocation_id_payment_method_timestamp",
|
"idx_merchant_location",
|
"idx_device_seq_number"
|
],
|
"key": "idx_merchant_transactions",
|
"key_length": "109",
|
"used_key_parts": ["merchant_id"],
|
"ref": ["const"],
|
"rowid_filter": {
|
"range": {
|
"key": "idx_device_seq_number",
|
"used_key_parts": ["device_id"]
|
},
|
"rows": 55,
|
"selectivity_pct": 4.785047e-5
|
},
|
"rows": 55,
|
"filtered": 4.785047e-5,
|
"attached_condition": "pt1_0.merchant_id <=> '<some id>' and pt1_0.merchant_id = '<some id>' and pt1_0.device_id = '<some other id>'"
|
}
|
}
|
}
|
The total amount of rows is thus 55 (out of 132.000.000), which are returned instantly when using any of the four possible indices, but takes minutes with ROWID filtering.
The same query without ROWID filter:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "pt1_0",
|
"access_type": "ref",
|
"possible_keys": [
|
"idx_merchant_transactions",
|
"idx_device_id_shoplocation_id_payment_method_timestamp",
|
"idx_merchant_location",
|
"idx_device_seq_number"
|
],
|
"key": "idx_merchant_transactions",
|
"key_length": "109",
|
"used_key_parts": ["merchant_id"],
|
"ref": ["const"],
|
"rows": 55,
|
"filtered": 4.783742e-5,
|
"attached_condition": "pt1_0.merchant_id <=> '<some id>' and pt1_0.merchant_id = '<some id>' and pt1_0.device_id = '<some other id>'"
|
}
|
}
|
}
|
{"report":{"fcp":971.6000000238419,"ttfb":268.10000002384186,"pageVisibility":"visible","entityId":128678,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"70cb1fa0-3e9c-4905-b2cc-abfa7f48c46d","navigationType":0,"readyForUser":1063.2000000476837,"redirectCount":0,"resourceLoadedEnd":685.6000000238419,"resourceLoadedStart":276.3000000715256,"resourceTiming":[{"duration":97.69999992847443,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":276.3000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":276.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":374,"responseStart":0,"secureConnectionStart":0},{"duration":101.70000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":276.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":276.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":378.3000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":289.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":276.8000000715256,"connectEnd":276.8000000715256,"connectStart":276.8000000715256,"domainLookupEnd":276.8000000715256,"domainLookupStart":276.8000000715256,"fetchStart":276.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":379.89999997615814,"responseEnd":566.3000000715256,"responseStart":410,"secureConnectionStart":276.8000000715256},{"duration":408.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":277,"connectEnd":277,"connectStart":277,"domainLookupEnd":277,"domainLookupStart":277,"fetchStart":277,"redirectEnd":0,"redirectStart":0,"requestStart":379.7000000476837,"responseEnd":685.6000000238419,"responseStart":404.39999997615814,"secureConnectionStart":277},{"duration":135.30000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":277.89999997615814,"connectEnd":277.89999997615814,"connectStart":277.89999997615814,"domainLookupEnd":277.89999997615814,"domainLookupStart":277.89999997615814,"fetchStart":277.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":380.2000000476837,"responseEnd":413.2000000476837,"responseStart":411.39999997615814,"secureConnectionStart":277.89999997615814},{"duration":142.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":278.3000000715256,"connectEnd":278.3000000715256,"connectStart":278.3000000715256,"domainLookupEnd":278.3000000715256,"domainLookupStart":278.3000000715256,"fetchStart":278.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":386,"responseEnd":420.39999997615814,"responseStart":415.2000000476837,"secureConnectionStart":278.3000000715256},{"duration":144,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":278.5,"connectEnd":278.5,"connectStart":278.5,"domainLookupEnd":278.5,"domainLookupStart":278.5,"fetchStart":278.5,"redirectEnd":0,"redirectStart":0,"requestStart":386.3000000715256,"responseEnd":422.5,"responseStart":420.5,"secureConnectionStart":278.5},{"duration":101.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":278.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":278.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":380.2000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":143.80000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":278.89999997615814,"connectEnd":278.89999997615814,"connectStart":278.89999997615814,"domainLookupEnd":278.89999997615814,"domainLookupStart":278.89999997615814,"fetchStart":278.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":389.8000000715256,"responseEnd":422.7000000476837,"responseStart":421.2000000476837,"secureConnectionStart":278.89999997615814},{"duration":103.20000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":279.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":279.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":382.3000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":147.10000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":279.2000000476837,"connectEnd":279.2000000476837,"connectStart":279.2000000476837,"domainLookupEnd":279.2000000476837,"domainLookupStart":279.2000000476837,"fetchStart":279.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":392.2000000476837,"responseEnd":426.3000000715256,"responseStart":422.8000000715256,"secureConnectionStart":279.2000000476837},{"duration":344.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":285.10000002384186,"connectEnd":285.10000002384186,"connectStart":285.10000002384186,"domainLookupEnd":285.10000002384186,"domainLookupStart":285.10000002384186,"fetchStart":285.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":422.39999997615814,"responseEnd":629.8000000715256,"responseStart":620.3000000715256,"secureConnectionStart":285.10000002384186},{"duration":345.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":285.10000002384186,"connectEnd":285.10000002384186,"connectStart":285.10000002384186,"domainLookupEnd":285.10000002384186,"domainLookupStart":285.10000002384186,"fetchStart":285.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":455.10000002384186,"responseEnd":630.3999999761581,"responseStart":626.3000000715256,"secureConnectionStart":285.10000002384186},{"duration":213.69999992847443,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":718.3000000715256,"connectEnd":718.3000000715256,"connectStart":718.3000000715256,"domainLookupEnd":718.3000000715256,"domainLookupStart":718.3000000715256,"fetchStart":718.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":888.6000000238419,"responseEnd":932,"responseStart":929.6000000238419,"secureConnectionStart":718.3000000715256}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":43,"responseStart":268,"responseEnd":274,"domLoading":274,"domInteractive":1133,"domContentLoadedEventStart":1133,"domContentLoadedEventEnd":1191,"domComplete":1675,"loadEventStart":1675,"loadEventEnd":1675,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1110.6000000238419},{"name":"bigPipe.sidebar-id.end","time":1111.3999999761581},{"name":"bigPipe.activity-panel-pipe-id.start","time":1111.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":1113},{"name":"activityTabFullyLoaded","time":1206.7000000476837}],"measures":[],"correlationId":"18cd9082a9494b","effectiveType":"4g","downlink":9.8,"rtt":0,"serverDuration":129,"dbReadsTimeInMs":32,"dbConnsTimeInMs":43,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
MDEV-26955 Improve the cost model for usage of in-memory range rowid / primary key filters
MDEV-29371 Rowid Filtering optimization can cause slowdowns
MDEV-34413 Index Condition Pushdown for reverse-ordered scans
