Details
Major Description
After upgrade on 10.4.26 after changing joined tables in WHERE clause the plan getting worse:
SELECT
|
subscriptionId, string, stringId, userId, webId, cron, lastAutoId
|
FROM subscriptions.send se
|
JOIN subscriptions.subscriptions s USING(subscriptionId)
|
JOIN subscriptions.profiles USING(profileId)
|
JOIN subscriptions.strings USING(stringId)
|
JOIN subscriptions.schedule USING(scheduleId)
|
WHERE next <= NOW()
|
AND se.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646) |
AND platform != 1 |
has the plan below:
1,SIMPLE,profiles,ALL,PRIMARY,NULL,NULL,NULL,2930162,100, |
1,SIMPLE,subscriptions,ref,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",profiles_ibfk_sub,4,subscriptions.profiles.profileId,1,100,Using where |
1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.subscriptions.scheduleId,1,100, |
1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.subscriptions.subscriptionId,1,100,Using where |
1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.subscriptions.stringId,1,100 |
The proper plan:
SELECT
|
subscriptionId, string, stringId, userId, webId, cron, lastAutoId
|
FROM subscriptions.send se
|
JOIN subscriptions.subscriptions s USING(subscriptionId)
|
JOIN subscriptions.profiles USING(profileId)
|
JOIN subscriptions.strings USING(stringId)
|
JOIN subscriptions.schedule USING(scheduleId)
|
WHERE next <= NOW()
|
AND s.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646) |
AND platform != 1 |
1,SIMPLE,s,range,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",PRIMARY,4,NULL,99,100,Using where |
1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.scheduleId,1,100, |
1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.s.subscriptionId,1,50,Using where |
1,SIMPLE,profiles,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.profileId,1,100, |
1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.s.stringId,1,100, |
On previous version 10.3.28 it works with proper plan regardless the table given in WHERE clause
Attachments
Issue Links
- relates to
-
MDEV-29538 Querry latency on some version of mariadb
-
- Closed
-