Database user permisions

[mc12345678]

Mentioned that the site is highly customized. Did that extra customization include additional database queries, and have those queries been structured to take advantage of the indexing (faster querying)?

[southshorepizza]

Yes. The customization is mainly read only queries and they are reading indexed columns and tables. However 2 computers read every 30 seconds to get updated information and are writing as needed between those but on a peak hour of 30 orders that writing is only between 40 and 50 writes in that hour. So no where near as many writes as there are reads.

[mc12345678]

Remember though, the fact that columns that are indexed and being read is not the same thing as the query being structured to take advantage of the indexing. The sequence of the queried fields can make or break the use of the indexing, as well as omitting one of the indexed columns when compared to the indices.

[southshorepizza]

Yes they are querried so the db narrows down the results as fast as possible and pulls as many from one query as possible.
Here is an example of the 30 second query

$sql = "SELECT orders_id FROM ".TABLE_ORDERS." WHERE date_purchased < NOW() AND orders_status IN (" . implode(',',$statuses) . ") ORDER BY orders_id ASC";

Limit by date purchased first because that eliminates the majority of the table. Then limit by status.

Here is another one and yes I plan on changing the * to more specifics in the near future.

$sql = 'select * from ' . TABLE_ORDERS . ' o left join ' . TABLE_HTA . ' h ON o.orders_id = h.order_id WHERE o.date_purchased < NOW() AND o.shipping_module_code LIKE "%Flat%" AND h.status = "MADE" ORDER BY o.orders_id ASC';

This one again limits by date. Then limits by shipping module which cuts out 60% of the orders. Then the made is the lowest percentage of finds.

[mc12345678]

Yes they are querried so the db narrows down the results as fast as possible and pulls as many from one query as possible.
Here is an example of the 30 second query

$sql = "SELECT orders_id FROM ".TABLE_ORDERS." WHERE date_purchased < NOW() AND orders_status IN (" . implode(',',$statuses) . ") ORDER BY orders_id ASC";

Limit by date purchased first because that eliminates the majority of the table. Then limit by status.

I'm not where I can review the indices for that table, but the use of the date comparison as provided doesn't help limit the quantity of returned rows at all... It simply returns all orders that have been made. There is no elimination of anything performed by looking for all orders purchased before now. (Even if the < was incorrect, there is/would be something missing to bound the data as there are no orders in the future...)

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

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.