Autojoined 'Options' field causes thousands of DB queries on a single page

[wet]

Experiencing performance issues I tried to dig into possible reasons, and this is what I found:

1. I had added an Options field to a template and checked 'Autojoin' because I wanted to sort by this field.
2. This template is used by several thousand pages in my site.
3. When one single page is rendered to the frontend, one database query for each of these 'sibling' pages is run. I.e. rendering a single page causes thousands of database queries.

The queries select the current option value for every sibling page of the current page. This is a snippet from the query log with the queries in question:

SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(4)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(2)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(3)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(5)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(5)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(5)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(4)
SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(3)
...and thousands more...

:fields_id never changes, is the id of the Options field. The queries are issued by ProcessWire\SelectableOptionManager::getOptions() .

Steps to reproduce:

1. Start with the stock 'site-default' site profile from the devns branch. 2.x might also work, I haven't tried.
2. Install TracyDebugger.
3. Set $config->debug to true.
4. Add an Options-type field (e.g. Select) with a handful of selectable options to the 'basic-page' template.
5. Create a few new pages using the 'basic-page' template.
6. View one of these pages in the frontend, open Tracey's 'PW Debug mode' panel and expand the 'PDO Queries ($database)' section.
7. Find one query per 'basic-page' page stating something like so:  SELECT * FROM fieldtype_options WHERE fields_id=:fields_id AND option_id IN(13)

I'd like to gather feedback from experienced developers with deeper understanding of the system:

1. Why does PW need the current option value of every other sibling page to render a single page?
2. Wouldn't it be possible to cache these redundant queries if they are needed in the first place?
3. How does this scale when the site grows?
4. How can I achieve sorting by an options field without causing such excessive query counts?

Please advise.

[wet]

Scratch that. It looks like Tracy itself causes these queries.

[adrian]

35 minutes ago, wet said:

Scratch that. It looks like Tracy itself causes these queries.

This doesn't sound good - can you narrow down where it's coming from in Tracy - is it due to the PW Info Panel where it populates the "Fields List & Values" section?

[wet]

This is the call stack up to ProcessWire\SelectableOptionManager::getOptions() :

I think it is pretty clear. Let me know if I can be of further help.

[adrian]

Hi @wet - based on the line number (120) shown for ProcesswireInfoPanel.inc it looks like it might be related to getting the previous page. I am wondering if maybe this is no longer a problem since: https://processwire.com/blog/posts/pw-3.0.24/

Could you please provide an update so I know if I still need to fix something in Tracy or not. If it is still a problem could you please confirm that it goes away if you disable the PW Info panel?

[wet]

This issue is no longer present with PW 3.0.25.