if order is shipped, move it to 'shipped' table to speed up database

[Guest]

what use is this?

when an order is marked 'shipped', drop all of the order details & history into a new table, leaving the default orders table empty of that order.

 

why?

less crap for to be scanned in the database whenever orders are being processed (printed, etc.) by the admin.

or in my scenario: i generate an orderlist for backorders every week. what this essentially does is scan my orders_* table for orders with the 'backorder' status. when there's a zillion orders in there, it tends to take eons for the list to be generated. why not skip the delay and get rid of the orders that will never need to be scanned again?

 

 

 

 

 

 

 

i thought i'd be able to wing this, but not so.

 

 

i got as far as:

creating the following tables (duplicates of 'orders_xx):

shipped_orders

shipped_orders_products

shipped_orders_products_attributes

shipped_orders_products_download

shipped_orders_status

shipped_orders_status_history

shipped_orders_total

 

 

and in admin/includes/database_tables.php:

define('TABLE_SHIPPED_ORDERS', 'shipped_orders');

define('TABLE_SHIPPED_ORDERS_PRODUCTS', 'shipped_orders_products');

define('TABLE_SHIPPED_ORDERS_PRODUCTS_ATTRIBUTES', 'shipped_orders_products_attributes');

define('TABLE_SHIPPED_ORDERS_PRODUCTS_DOWNLOAD', 'shipped_orders_products_download');

define('TABLE_SHIPPED_ORDERS_STATUS', 'shipped_orders_status');

define('TABLE_SHIPPED_ORDERS_STATUS_HISTORY', 'shipped_orders_status_history');

define('TABLE_SHIPPED_ORDERS_TOTAL', 'shipped_orders_total');

 

 

 

 

 

now when it comes to admin/orders.php, what i want to do:

- when an order is marked as 'shipped' via the status pull-down menu (which is default osc), send this order data to the shipped_* table instead of just marking the status as shipped and leaving it there to clog the orders table.

 

how could i do this?

i am guessing maybe modifying something from this line (which is default osc):

case 'update_order':
$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);
$status = tep_db_prepare_input($HTTP_POST_VARS['status']);
$comments = tep_db_prepare_input($HTTP_POST_VARS['comments']);

 

and/or:

if ( ($check_status['orders_status'] != $status) || tep_not_null($comments)) {
tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . tep_db_input($status) . "', last_modified = now() where orders_id = '" . (int)$oID . "'");

 

or even:

tep_db_query("insert into " . TABLE_ORDERS_STATUS_HISTORY . " (orders_id, orders_status_id, date_added, customer_notified, comments) values ('" . (int)$oID . "', '" . tep_db_input($status) . "', now(), '" . tep_db_input($customer_notified) . "', '" . tep_db_input($comments) . "')");

$order_updated = true;
}

if ($order_updated == true) {
$messageStack->add_session(SUCCESS_ORDER_UPDATED, 'success');
} else {
$messageStack->add_session(WARNING_ORDER_NOT_UPDATED, 'warning');
}

 

 

and to be clear: i want to still remain using the default osc order tables until the order is actually marked shipped

 

 

can anyone offer any ideas? i'm sure this could end up being large work so i'm not asking for anyone to hold my hand, but i just need a little direction. i'm sure others could find this useful as a contribution once completed :)

 

 

what code(s) can i use to use the shipped_* tables for orders that have been marked shipped?

[Guest]

The correct answer is to add proper indexes to your tables so that they join more efficiently and hence faster.

 

Bobby

[Guest]

The correct answer is to add proper indexes to your tables so that they join more efficiently and hence faster.

 

Bobby

that is a band-aid solution to my speed problem. :)

 

why dig through those orders that will never need to be seen again?

[Guest]

calling all php wizards, i'm sure some of you could achieve this function with your eyes closed :)

[♥Monika in Germany]

I suggest you look at Amanda's nice email queue contrib ... it has a great history feature that you can use for pointers.

http://www.oscommerce.com/community/contri...rch,email+queue

 

Your plan is correct if you absolutely want to move tables, but remember that your customers will want to check for older orders also, which will then result in 2 queries (not to mention the history table will expand).

 

Instead, I'm all for correct indexing and frequent analyzing/optimizing the database instead, just like Bobby suggested.

[Guest]

I suggest you look at Amanda's nice email queue contrib ... it has a great history feature that you can use for pointers.

http://www.oscommerce.com/community/contri...rch,email+queue

i installed this the other day and never thought to check. but i would imagine it's simpler as there is only 1 query for emails, right?

 

how do i tell the query that if the status is 'shipped', send it away; else go to the normal query? this is the most troubling part.

 

Your plan is correct if you absolutely want to move tables, but remember that your customers will want to check for older orders also, which will then result in 2 queries (not to mention the history table will expand).

once i get the admin-end working i was going to fix it up on the customer account page, so that first logon gives them the fresh order(s), but if they want to see their history they click to a page that will then scann the shipped_* tables

[♥Monika in Germany]

get the id of that status, and do an if/else ... plenty of examples in any code ...

[boxtel]

i installed this the other day and never thought to check. but i would imagine it's simpler as there is only 1 query for emails, right?

 

how do i tell the query that if the status is 'shipped', send it away; else go to the normal query? this is the most troubling part.

once i get the admin-end working i was going to fix it up on the customer account page, so that first logon gives them the fresh order(s), but if they want to see their history they click to a page that will then scann the shipped_* tables

 

well, the archive facility in the email queue was setup because emails, by nature, are a one off thing. You need to be able to check if they were send and revisit the content if there are any disputes but other than that, send emails are dead weight. Furthermore, when people use the queue for newsletters of mass-mailings, that can add up pretty fast.

 

Ofcourse, every osc store has to consider archiving orders at some point, even a low volume site must consider this after some years of operation. Still, I would never archive at shipped status. I set my order status to shipped when I ship. But what if the shipment does not arrive, is broken, is not wanted,...

Then you may have to alter the status again, send additional information, change the order content, etc.

That is not handy if the order information is in an archive table.

 

So if you archive, use a scheduled job which selects all shipped orders which have not been updated for say three months. That is much more solid, does not interfere with your normal order status handling and I bet not many clients are actually interested in viewing their completed orders from 3 months ago.

[Guest]

Still, I would never archive at shipped status. I set my order status to shipped when I ship. But what if the shipment does not arrive, is broken, is not wanted,...

Then you may have to alter the status again, send additional information, change the order content, etc.

That is not handy if the order information is in an archive table.

 

So if you archive, use a scheduled job which selects all shipped orders which have not been updated for say three months. That is much more solid, does not interfere with your normal order status handling and I bet not many clients are actually interested in viewing their completed orders from 3 months ago.

i have never thought of it in this sense. how could one go about doing this? the logic is well beyond my current level of skill, i haven't the slightest clue as to how to approach this method instead of my initial one

[boxtel]

i have never thought of it in this sense. how could one go about doing this? the logic is well beyond my current level of skill, i haven't the slightest clue as to how to approach this method instead of my initial one

 

The logic is simpler than your original approach.

 

simply schedule a job every week.

 

in that job you select all orders of status shipped and/or cancelled and a last update stamp of 3 months ago.

you insert them 1 on 1 (no auto_increment in archives) in the archive tables and delete them from the originals.

 

then you can start altering the display screens for admin and/or your customers to view the archived order information if required.

 

this way you separate system maintenance (archiving) from real time shop operations.

[Guest]

that is a band-aid solution to my speed problem. :)

 

why dig through those orders that will never need to be seen again?

To the contrary...I have several clients with 250,000+ orders and with proper table indexes the admin tool is requires less than .1 seconds total MySQL time (for orders).

 

Not to blow my own horn but I've speed optimized more sites than I can count and literally wrote the book on the subject (with respect to osC and high volume sites). I agree that archiving is best for intensive data aggregation (reports, etc) but not for simple orders administration.

 

Bobby aka "Chemo" (hopefully that won't get this account banned)

 

BTW, it's nice to talk to see all of you again :)

[Guest]

The logic is simpler than your original approach.

 

simply schedule a job every week.

 

in that job you select all orders of status shipped and/or cancelled and a last update stamp of 3 months ago.

you insert them 1 on 1 (no auto_increment in archives) in the archive tables and delete them from the originals.

 

then you can start altering the display screens for admin and/or your customers to view the archived order information if required.

 

this way you separate system maintenance (archiving) from real time shop operations.

i have to agree :)

whereabouts could i find some guidance on how to firstly build the proper queries for such a thing? (maybe an existing contribution that works similarly to what i need to achieve?) there's so many tables involved with customers & orders .

 

i know it's just basic php, but i am only able to build things by example (aka i suck)

:)

 

 

Not to blow my own horn but I've speed optimized more sites than I can count and literally wrote the book on the subject (with respect to osC and high volume sites).

speed optimization is certainly an esstential, but in my train of thought i do not see a point to put more load on the sql server than needed

 

now i have been told several times by several different people to do these indexes. i have also been told by others that don't put an index on something 'unless you absolutely need to' - confusing.

 

to be specific, what tables need to be indexed??