Database user permisions

[mc12345678]

Again, and I realize the post was edited after I began response, the date "filter" does not eliminate anything. The "60% reduction" comes from the like and b.status portions of the query. It also does not prove that indexing is being optimized and I am still unable to identify the indices available as I'm not at a computer nor able to "easily" pull up the index list for the orders table.

[mc12345678]

Would want something like date_purchased > DATE(CURRENT_DATE() - INTERVAL 2 WEEK)

Which is an example of 2 weeks ago, would need to find the interval information necessary to accomodate your criteria.

[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]

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.

[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