While trying to query revision table based on comments in toolforge environment, I noticed awful performance for simple query like:
select rev_id, comment_text from revision inner join comment on rev_comment_id=comment_id where comment_text like '%CSD#G11%' limit 10;
Following the good work in T166733 and related tasks, I think it may be cool to have revision_commentindex similar to revision_userindex in Toolforge for use by tools that query based on comments.
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | daniel | T161671 Compacting the revision table | |||
| Resolved | None | T227047 Complete actor table and comment table migration | |||
| Open | None | T6716 Edit summary box should be a multiline input | |||
| Open | None | T208690 Create revision_commentindex on Wiki Replicas in Cloud VPS |
I note there are likely three sources of slowness here:
This request would help with #3. #2 is being discussed as T215445: comment and actor view challenges for Cloud Services. I doubt there's anything we can do about #1, that part will always be slow.
Improving #2 and #3 would be a big win I think. On the bigger wikis, these queries currently time out for me after an hour or so.
Can we get anywhere by using the comment_revision view instead of comment? https://wikitech.wikimedia.org/wiki/News/Actor_storage_changes_on_the_Wiki_Replicas#The_actor_table_seems_really_slow--so_does_comment
Revision is a weird table (because of the temp table), so I'm curious if that will help @Anomie
That table only needs to check revision instead of 7 other subqueries.
Using comment_revision would help with #2 by reducing the number of subqueries needed.
I'd rather not make a slippery slope argument, but I must admit it's there in my mind. Revision is the table where I'd want this kind of thing vs. probably anywhere else.
We are proliferating views rather quickly lately, and the setup is becoming steadily more complex to maintain and use. The value of a commentindex view will drop off entirely once the temp table is no longer there, so we'd want to leave a comment to that effect, I think, so that when the aliasing is dropped, the commentindex view is deprecated (and dropped with warning) as well.
Barely. The simplest WHERE I can imagine still takes 15 min:
SELECT comment_id FROM comment_revision WHERE comment_id > 284004931 AND comment_text LIKE "Bluelink%"
If we cannot even scan 30M rows out of 300M and counting, how is one ever supposed to be able to query the comment table, let alone join it with something else?