Cache queries in admin

[radhavallabh]

Hi dear;
Is it possible we could cache the queries admin side;
Because while deleting a product or attribute I get a max_question fatal error so working on the admin side with many products and attributes has become next to impossible.

I cannot set max_question in sql to 0 as am on a shared hosting. And the host said it wont be possible.
I would like to mention my Oscommerce Frozen version is working very smoothly on the same server till date with same number of products and attributes.

So is there a way we could cache the queries made to the database- so it becomes minimal while using and working becomes smooth.
It gives a Fatal Error every time I try deleting, editing of products or attributes
Fatal error: DB: [1226] User 'xx' has exceeded the 'max_questions' resource (current value: 150000) from

A solution for above will be be awesome dear and will be very grateful...
Thank you in advance;
Very Warm Regds./
radhavallabh

[ecartz]

Your host might be more willing to set max_questions for your user to something higher than 150000 but lower than infinite (0). This value can be set per user.

You also might consider alternate hosts.

An alternative possibility is to change the users that you are using. See https://stackoverflow.com/a/53978743/6660678

Note that this only works if your host allows you to create your own database users (some do and some don't). At minimum, you would want to create separate users for admin and catalog (so catalog keeps working even when admin breaks).

If you number the users, you can do something like

Code: Select all

define('DB_SERVER_USERNAME', 'dbuser' . rand(0, 3));

Adjust dbuser and the numbers as necessary to match what you have. My suggestion would be to do that only in admin and have a different (unnumbered) user for catalog. Easiest being for catalog to have the same user it has now.

Remember when setting up the users to give all of them the same password.

If this is only happening on the attributes page, you might consider using the AJAX Attribute Manager or Easy Populate to manage your attributes. Easy Populate moves most of the handling off the server, so max_questions wouldn't necessarily apply. AJAX Attribute Manager is an alternative handler. Rainer might be willing to create a Pro (paid) version that offers some kind of caching if it isn't already more efficient.

It would be difficult to add caching into core. Since this isn't something that most people hit, it is unlikely that we are going to prioritize "fixing" it. For most people, the correct response to not being able to change max_questions is going to be to switch to a different server (and possibly a different host).

[radhavallabh]

Your host might be more willing to set max_questions for your user to something higher than 150000 but lower than infinite (0). This value can be set per user.

You also might consider alternate hosts.

An alternative possibility is to change the users that you are using. See https://stackoverflow.com/a/53978743/6660678

Note that this only works if your host allows you to create your own database users (some do and some don't). At minimum, you would want to create separate users for admin and catalog (so catalog keeps working even when admin breaks).

If you number the users, you can do something like

Code: Select all

define('DB_SERVER_USERNAME', 'dbuser' . rand(0, 3));

Adjust dbuser and the numbers as necessary to match what you have. My suggestion would be to do that only in admin and have a different (unnumbered) user for catalog. Easiest being for catalog to have the same user it has now.

Remember when setting up the users to give all of them the same password.

If this is only happening on the attributes page, you might consider using the AJAX Attribute Manager or Easy Populate to manage your attributes. Easy Populate moves most of the handling off the server, so max_questions wouldn't necessarily apply. AJAX Attribute Manager is an alternative handler. Rainer might be willing to create a Pro (paid) version that offers some kind of caching if it isn't already more efficient.

It would be difficult to add caching into core. Since this isn't something that most people hit, it is unlikely that we are going to prioritize "fixing" it. For most people, the correct response to not being able to change max_questions is going to be to switch to a different server (and possibly a different host).

I already used the multiple user fetch for my database in configure.php...
But it still gives same error while deleting 2 or more products at one go every time with different usernames...
Switching the host will be a problem dear any other solution please.....

I would be so glad if we could get a caching like solution we had with oscommerce frozen....
Thank you so much for your valuable reply;
Regds./
radhavallabh

[radhavallabh]

Your host might be more willing to set max_questions for your user to something higher than 150000 but lower than infinite (0). This value can be set per user.

You also might consider alternate hosts.

An alternative possibility is to change the users that you are using. See https://stackoverflow.com/a/53978743/6660678

Note that this only works if your host allows you to create your own database users (some do and some don't). At minimum, you would want to create separate users for admin and catalog (so catalog keeps working even when admin breaks).

If you number the users, you can do something like

Code: Select all

define('DB_SERVER_USERNAME', 'dbuser' . rand(0, 3));

Adjust dbuser and the numbers as necessary to match what you have. My suggestion would be to do that only in admin and have a different (unnumbered) user for catalog. Easiest being for catalog to have the same user it has now.

Remember when setting up the users to give all of them the same password.

If this is only happening on the attributes page, you might consider using the AJAX Attribute Manager or Easy Populate to manage your attributes. Easy Populate moves most of the handling off the server, so max_questions wouldn't necessarily apply. AJAX Attribute Manager is an alternative handler. Rainer might be willing to create a Pro (paid) version that offers some kind of caching if it isn't already more efficient.

It would be difficult to add caching into core. Since this isn't something that most people hit, it is unlikely that we are going to prioritize "fixing" it. For most people, the correct response to not being able to change max_questions is going to be to switch to a different server (and possibly a different host).

Hi dear @ecartz
I was going through the older suggestions for database query solutions in oscommerce forums-
I came across this post solution for a issue someone was facing on orders.php pages

Talked with a DBA buddy of mine, and offered him a prize to fix this... he came back in about 20 minutes with a fix that took my query time down from ~49 seconds, down to 0.14 seconds. This fix is 3 additional indices:

create index products_id_orders_id on orders_products (products_id, orders_id);
create index products_id on orders_products (products_id);
create index orders_id on orders_products (orders_id);

The second and third indices are optional. The first one was THE key. That took the query down to 1.03 seconds. The next two took me down to 0.16 seconds and 0.14 seconds.

Can we have similar index solution for Phoenix that could reduce database queries dear;

Please can you help me with this;
Thank you in advance;
Very Warm Regds./
radhavallabh

[burt]

Use phpmyadmin to run those SQL on your database. If you dont know how, ask your host.

After you have tested your site with those changes, report back to let us know of any difference in your site speed etc

[ecartz]

Can we have similar index solution for Phoenix that could reduce database queries dear;

Indexes don't reduce the number of database queries. Indexes make existing queries run faster.

I would be a bit leery of those recommendations, as

Code: Select all

create index products_id_orders_id on orders_products (products_id, orders_id);
create index products_id on orders_products (products_id);

is nonsense. The second index there is completely useless given the first index. There is no reason to have both indexes. The redundant index will only make inserts slower. It won't make other queries faster.

It's also worth noting that we already have the latter two indexes. So if you want this, just modify one of the indexes to cover both columns and you'll get the same benefit. I think that it makes more sense to make orders_id, products_id the compound index, as you are more likely to be looking up the product IDs from the order IDs. Also, on a busy site, I would expect there to be more order IDs than product IDs.

But again, this wouldn't fix the error message that you are getting.

Caching also has a similar problem. You are describing a problem in admin. But admin didn't use caching. Caching was only used in catalog. If you already use different database users in admin and catalog, the old cache system would do absolutely nothing for a problem in admin.

No caching on the Frozen products attributes page: https://github.com/CE-PhoenixCart/Phoen ... ibutes.php

[radhavallabh]

Hi dear @ecartz @burt
Sorry for troubling so much,
If not cache can we have a different solution for below please?

I switched on Parse Time and queries in admin-

It prints that loading of admin/catalog.php takes 14.5 seconds which is too long for a fully loaded shop... Is it possible we could do something else to bring this down??

Sorry for being persistent but dear it is really needed for stores that would keep expanding the catalog...

Thank you in advance;
Very warm Regds./
radhavallabh

Screenshot 2022-06-02 at 15-05-59 CE Phoenix Cart Administration Tool.png

[burt]

Sorry for being persistent but dear it is really needed for stores that would keep expanding the catalog...

This is where a certified developer would help you, and you might then put any code changes back to the project.

[ecartz]

You could also try turning on the slow queries log in MySQL and reporting any queries that take longer than a second to process.

[heatherbell]

Seems that this is resolved here:
app.php/addons/free_addon/qtpro/support ... 1786#p1786