The “username” was meant to be between [ xyz ], not sure why it didn’t display.
The problem is with the sql queries. I use the hashed value of the permalink to search the table. Those fields have an index on them. Using a wildcard search on a sql text field will slow down the queries considerably. I can look at adding an option to use wildcards. I was planning on adding that. Then the user can turn it off if the queries get too slow. On a small table it shouldn’t really cause a problem. But as your table gets bigger you’ll lose the benefits of disabling plugins. I’ll try to add that in the next release.
Hey Jeff,
Thank you a lot for the answer and explanation. Mmmm… Indeed our table might get big, we plan on having a couple hundreds thousands users.
I guess for now it’ll be fine. I’m not sure I deeply understand the intricate relation between using a wildcard search and the number of sql queries, but is there any hope for some sort of logic that would avoid this problem in the long term?
All best,
Bastien
Its a limitation in sql. You can’t use an index on a text field when using wildcards. It does an entire table scan for every query. So in other words it reads every row of the table for every page load which takes a long time.
Ok well then isn’t there a way to achieve this without using a wildcard? For example, by storing the username in some sort of constant?
Which table is the plugin scanning? How is it getting bigger? Why would it need to read every row?
I’m sure if there was a way you’d probably have thought about it by now but performance is a major concern to me and plugin organizer is a key component in this matter.
All best,
Bastien
There is an entry in the PO_plugins table for every post that has plugins disabled/enabled on it. If you do an sql query that looks for a permalink matching http://www.yoursite.com/%/forums then it would bring back all of the rows that match that wildcard. But the sql engine would have to read every row in the table and match the string against the permalink field to see if it should be returned or not. Using an index on the field would be useless since sql doesn’t use an index in that kind of statement. The table contains the hashed value of the permalink and there is an index on those fields. When an index is used the sql engine doesn’t have to read in every row to find out where a matching value is. It can go right to the row that matches. Think of an index as a table of contents in a book. Without it the engine has to read through the whole book to find out which chapter contains info on a specific subject.
Jeff,
Thank you a lot for your detailed explanation. I think I’m getting a grip of the background of the plugin. If I understand well, when you said this:
But as your table gets bigger you’ll lose the benefits of disabling plugins.
The table we’re speaking of right now is the table that list all the URLs that should be optimized with PO, right? In order to make sure this table doesn’t get too big, is it possible that PO urls are stored in an unique table depending on the group it is associated with?
Example : I have a bunch of rules in PO settings, most of them can be separated into categories/groups : Admin rules, Single-post rules, Page rules, Category / Archive page rules, Buddypress rules (wildcard).
[…]the sql engine would have to read every row in the table and match the string against the permalink field to see if it should be returned or not.
If I would be able to create groups for PO rules that would split the PO table into different tables, would it be possible to make sure this process is optimized? Does what I’m saying makes any kind of sense at all?
Looking forward to hear back from you.
All best,
Bastien
