Page MenuHomePhabricator

Unable to use `force index` on replicas due to view layer intervention (Key 'PRIMARY' doesn't exist in table 'page')
Closed, DeclinedPublic

Description

Hi,

Some requests are very slow on replicas, I think it is using the wrong index.

use enwiki_p;
select count(*) from `page` where `page_id` between 6000000 and 6999999 and page_is_redirect=0
Executed in 1.08 seconds
use enwiki_p;
select count(*) from `page` where `page_id` between 6000000 and 6999999 and page_namespace=6 and page_is_redirect=0
Executed in 112.27 seconds

I try to use force index but it doesn't work :

use enwiki_p;
select count(*) from `page` force index (PRIMARY) where `page_id` between 6000000 and 6999999 and page_namespace=6 and page_is_redirect=0
Key 'PRIMARY' doesn't exist in table 'page'

I saw the same problem when I try to use rev_timestamp index on revision table.

How can I use force index with replicas ?

Event Timeline

On which host(s) are you running the above queries?

You cannot use FORCE (or USE or IGNORE) as those are views and not really tables.

I used https://quarry.wmflabs.org to test those queries on enwiki_p.

It is very penalizing to not be able to force the use of indexes.

What is the right method to work around when the optimizer takes the wrong index? Do you have to remove conditions, read the fields and do the counting at the application level?

I used https://quarry.wmflabs.org to test those queries on enwiki_p.

It is very penalizing to not be able to force the use of indexes.

What is the right method to work around when the optimizer takes the wrong index? Do you have to read the fields and count at the application level?

Unfortunately we cannot fix it, as it is a "feature" of the views: https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/view-restrictions.html

bd808 renamed this task from Unable to use force index on replicas (Key 'PRIMARY' doesn't exist in table 'page') to Unable to use `force index` on replicas due to view layer intervention (Key 'PRIMARY' doesn't exist in table 'page').Jul 19 2020, 11:43 PM
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.

This looks like it was meant to be closed, but perhaps not marked so. Is there anything else to be done with this ticket?

Does not look like.