Database user permisions

[southshorepizza]

Actually that is part of that customization. There are future orders. But the query should be tweaked so it's between 00:00:00 and now.

[southshorepizza]

date_purchased BETWEEN DATE(CURRENT_DATE() - INTERVAL 1 DAY) AND NOW() ORDER BY orders_id DESC;

[mc12345678]

date_purchased BETWEEN DATE(CURRENT_DATE() - INTERVAL 1 DAY) AND NOW() ORDER BY orders_id DESC;

If not mistaken that pulls from the day before to now, instead of the start of today to now... Also I thought I read something about mixing the date/format types might be an issue (DATE versus datetime)

[southshorepizza]

Yes that pulled the last 24 hours. I like yours better so I just updated it to your suggestion. Thank you. It still seems to be taking the same amount of time to load the page. Maybe because we don't have a lot of data being returned maybe because of the host. But your query is definitely better than what was there.

[mc12345678]

Also, these are the indices that I found on a ZC 1.5.3 install:

Keyname
Type
Cardinality
Field

                 PRIMARY
                 PRIMARY
                 18
orders_id             

                 idx_status_orders_cust_zen
                 INDEX
                 18
                orders_status             

                 orders_id             

                 customers_id             

                 idx_date_purchased_zen
                 INDEX
                 18
date_purchased             

                 idx_cust_id_orders_id_zen
                 INDEX
                 18
customers_id             

                 orders_id

So basically like the idx_status_orders_cust_zen index gets used when the query has WHERE orders_status = xxx and orders_id = yyy and customers_id = zzz, so yes by using the date_purchased field first at least one index is used...

But, it seems that the queries that are taking the longest are ones that don't really limit the results, but instead ask for all orders that are in the past.

[southshorepizza]

Thank you. Those two I posted are the ones that query twice a minute. I will set the other one up that I posted the same as the one we just discussed. Otherwise the queries should not be that stressful as order taking would be the majority of all other queries. We run reports every now and then but that would be maybe 10 or 15 in an entire day. So back to the original questions. Is it possible that the tables are on the wrong engine and need to be switched as there is too much activity to support table locking or is the hosting company giving a cop out to poor server performance?

[southshorepizza]

According to hostgator queries such as this one keep timing out. Can anyone give some guidance as to what it is actually used for? Is it the 15 minute admin log in? When looking at my site they say this is constantly an issue.

select value from zen_sessions where sesskey = '49ef1a7910977c50ba19085ec8cffcea' and expiry > '1425149368'

Thanks

[RodG]

According to hostgator queries such as this one keep timing out. Can anyone give some guidance as to what it is actually used for? Is it the 15 minute admin log in? When looking at my site they say this is constantly an issue.

select value from zen_sessions where sesskey = '49ef1a7910977c50ba19085ec8cffcea' and expiry > '1425149368'

Thanks

I guess the simple answer here is that this is used to timeout any session data, whether it be for the customer or user logins or site accesses.

This is also unimportant information. The fact that the query itself is causing a server timeout (which I assume is what hostgator are telling you) IS important, and in my experience it generally means that the Sessions database table has become corrupted.

You can check/rectify this by running the 'repair database' function provided by phpmyadmin, the 'MySql database' tool found in most cPanels, or almost any other DB administration tool at your disposal.

Cheers
RodG

[mc12345678]

Actually that is part of that customization. There are future orders. But the query should be tweaked so it's between 00:00:00 and now.

Untested, but:

DATE(date_purchased) = DATE(CURRENT_DATE()) and date_purchased < NOW()

Would query on all orders for the current date (current and "future", assume though that you have more past orders than future orders.