Jump to content
Latest News: (loading..)

immortaldiamond

Members
  • Content count

    5
  • Joined

  • Last visited

  1. Optimizing db query to speed up orders.php

    Thanks for the responses; I appreciate it, and apologize for my initial aggressive and/or cocky tone. I'm proud of the progress I've made, but I need to learn to accept criticism better. I've been a bit defensive about moving to php 7, but honestly, that's just because I have no other choice at this point. I know it is far from an ideal choice. @clustersolutions, I think you're right about having an extra desc in there--in fact, since there is only one entry for each order id, the final group by and order by are unnecessary, right? I don't quite understand your other suggestion. Is an inner join inherently better than a left join? I was just copying the original code and didn't see a reason to change it. I think the indexes are good now, but here's the explain for the new query. I assume I can't have an index on a derived table? id select_type table type possible keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 1 PRIMARY o eq_ref PRIMARY PRIMARY 4 x.orders_id 1 1 PRIMARY s ref PRIMARY PRIMARY 4 name_db.o.orders_status 1 2 DERIVED ot index class idx_orders_total_orders_id 4 NULL 73 Using where As for innodb, I'm not going to switch over now, since I have the query working for my purposes, but I do see the benefits. What would be involved? Can I just switch the table to use the other engine, or would that have further repercussions? @MrPhil, most (all?) of what I have done to prep for PHP 7 is replace functions that are no longer supported. I'm not able to spend the time to take advantage of php 7's new capabilities, unfortunately. I've gone through the list of discontinued functions on the php website, looked at blog posts, forum threads here, etc. I've also done extensive testing of front and back end of the website. Are there other things I should be looking out for/common pitfalls you are aware of that might only appear later down the road? Also, thanks for the advice on migrating to another platform. It's been a looming question at my company for the last five years or so, and I do want to go in with my/our eyes open. We'd love to stick with osCommerce, since we are familiar with the look and feel of it. The problem is, there seem to be very few professional web developers who are familiar with it. As you see it, is the use of osC continuing to decline? The website we have now is so heavily modified by so many different developers (many of them pretty incompetent, imho, but that was before my time) that it would be a big fat mess even to someone familiar with osCommerce. I know that a lot of plugins have been developed that would probably take care of a lot of the modifications in a much cleaner way. My concern is that I see many plugins that do not get updated for years, and I assume would become obsolete with new versions of osCommerce. My hope is that by migrating to a platform with a larger and/or increasing user base we would a) have more options for professional developers and b) have a wider range of well-maintained plugins to choose from, and thus be able to avoid modifications that would soon become a maze of mysterious code and land us right back with an obsolete code base that no one knows how to deal with. I'm not trying to bash osC--it has served us well for many years, and I would even be relieved to stick with it. I'd love to hear your thoughts on this situation, though perhaps a new thread would be more appropriate.
  2. Optimizing db query to speed up orders.php

    Yay, I restructured the query so it works! To simplify things, I did take out the check for language. It's probably easy enough to add back in, but I didn't spend time on it because I only use english. @clustersolutions--Thank you so much for your suggestion to do an explain! It got me on the right track. Turns out it was not a deadlock like I thought; instead, it was trying to go through over 100 billion rows (if I understand the explain correctly)! I'll include it below in case it's of interest. I don't understand mysql enough to know exactly why one version of mysql is processing the query so differently from the other, but I found a way around it by restructuring the query to work on both versions. Here is the final query, formatted a bit for clarity. My expertise is in googling, so I'm sure it can still be improved. SELECT o.approved, o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, x.text FROM (SELECT * FROM orders_total ot WHERE ot.class = 'ot_total' ORDER BY ot.orders_id DESC LIMIT 0,20) AS x LEFT OUTER JOIN orders AS o ON o.orders_id = x.orders_id LEFT OUTER JOIN orders_status AS s ON o.orders_status = s.orders_status_id GROUP BY x.orders_id ORDER BY x.orders_id DESC I did have to tweak the page split function so it could add the limit in the middle of the query instead of at the end. The following is my un-graceful solution, commented for clarity. This is the edited portion of orders.php, including the query before the limit is added: if (isset($_GET['cID'])) { // query not changed here } elseif (isset($_GET['status'])) { // this query not changed either } else { // note that "o.approved" is part of a modificaton and is not part of the standard code $orders_query_raw = "select o.approved, o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, x.text from (select * from " . TABLE_ORDERS_TOTAL . " ot where ot.class = 'ot_total' order by ot.orders_id DESC) as x left outer join " . TABLE_ORDERS . " as o on o.orders_id = x.orders_id left outer join " . TABLE_ORDERS_STATUS . " as s on o.orders_status = s.orders_status_id group by x.orders_id order by x.orders_id DESC"; // add simple query for number of orders so we don't have to process the complex query just for this $orders_query_count="from orders"; } // edited to include the $orders_query_count $orders_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $orders_query_raw, $orders_query_numrows, $orders_query_count); And split_page_results.php: // this is the __construct function in updated versions of osC function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows, &$count_query='') { if (empty($current_page_number)) $current_page_number = 1; // added "if" around default code so it only applies if count_query value not passed if (empty($count_query)) { $count_query = $sql_query; $pos_to = strlen($sql_query); $pos_from = strpos($sql_query, ' from', 0); $pos_group_by = strpos($sql_query, ' group by', $pos_from); if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by; $pos_having = strpos($sql_query, ' having', $pos_from); if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having; $pos_order_by = strpos($sql_query, ' order by', $pos_from); if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by; } else { // added for when count_query IS used $pos_to = strlen($count_query); $pos_from = 0; } $reviews_count_query = tep_db_query("select count(*) as total " . substr($count_query, $pos_from, ($pos_to - $pos_from))); $reviews_count = tep_db_fetch_array($reviews_count_query); $query_num_rows = $reviews_count['total']; $num_pages = ceil($query_num_rows / $max_rows_per_page); if ($current_page_number > $num_pages) { $current_page_number = $num_pages; } $offset = ($max_rows_per_page * ($current_page_number - 1)); // here's the hacky part--adding the limit after the first DESC, instead of at the end of the query if ($query_num_rows > 0 && strpos($sql_query, 'DESC')) { $limit = " limit " . $offset . ", " . $max_rows_per_page; $pos_desc = strpos($sql_query, 'DESC') + 4; $sql_query = substr_replace($sql_query, $limit, $pos_desc, 0); } elseif ($query_num_rows > 0) { // unless no DESC in query, then add limit at the end $sql_query .= " limit " . $offset . ", " . $max_rows_per_page; } } For anyone interested, this is the explain result for my first attempt at modifying the query. Yes, the orders table has 113,920 rows. id select_type table type possible keys key key_len ref rows Extra 1 PRIMARY o ALL NULL NULL NULL NULL 113920 Using temporary; Using filesort 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 113930 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 4 3 DERIVED s ALL NULL NULL NULL NULL 4 Using where 2 DERIVED ot ref class class 34 96184 Using where
  3. Optimizing db query to speed up orders.php

    You are quite right about clinging to an old release. I am only trying to make it last until we can move to something else, possibly magento. We tried wordpress and it simply wasn't powerful enough. Updating to PHP 7 is purely from necessity, being forced into it by our hosting provider. It was a worth-while upgrade though--it only took about a week, mostly just changing mysql to mysqli. Improving the orders page is certainly not essential, but since I got it working locally I hoped to be able to implement it now. On the live website, the new query I worked out is causing database deadlock. It's quite possible there is a bug in the MySQL version I'm using. I'll keep looking into it, and post an update if I figure out a solution that might help someone else.
  4. Optimizing db query to speed up orders.php

    @ArtcoInc I appreciate your fast response. I must disagree that I am "basically past the point that a osC person can support me anymore," and I would argue that resolving this issue would help everyone using osC. If you actually look at the files in the Edge release that you praise so highly, you will notice that the queries on orders.php and split_page_results.php are almost identical to those I am dealing with. I have been referring to the Edge release extensively as I work on this, but I have been repeatedly surprised and disappointed at how similar it is to what I am running. This is the query from the edge version, orders.php line 457 (https://github.com/gburton/Responsive-osCommerce/blob/master/admin/orders.php): $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC"; And this is the query on my live website, from osC 2.2-MS2: $orders_query_raw = "select o.approved, o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC"; You will notice that the ONLY change is the addition of "o.approved" at the very beginning of the select statement. This is a binary field that should make no difference in the speed of the query. The query itself is just clumsy; this has nothing to do with the version of osC I am running. I apologize if I am taking this too seriously, or if I have misinterpreted the intent of your reply. I know that I am pushing my build of osC to its limits, but I'm doing what I have to do to make it last until we can try again for a new website--my company has already attempted this twice, both times ending in disaster. But I think this can be beneficial to the osC community, as it brings to light areas could really use improvement. You may be right that a mysql forum would be better able to address this issue. But if I do find an answer somewhere else, I definitely want to share it with the rest of the osC community.
  5. Howdy, y'all. Would someone who as experience with mysql take a look at my query for order lookup to identify why it might be causing the orders table to lock (deadlock?). I'm running a very, very modified store that started out as 2.2-MS2. I'm working on updating the site to move from php 5.4 to php 7, and optimizing/fixing errors at the same time. The live website is running mysql 5.5, and I've set up my localhost with mysql 5.7. Right now I'm trying to speed up the admin order overview page, orders.php. Currently, it takes 3-5 seconds to load. Good news is, on my localhost I modified the query to cut the load time down to .07 seconds! But the switch to php 7 won't happen for a few months, so I really want to apply this change now to the live website. I tried changing the query on the live website, but the orders table in the database locked and orders.php simply wouldn't load until I reverted the changes and had mysql restarted. Can anyone tell me why this would happen? I'm particularly hesitant to experiment because I have to contact my host to restart mysql. Old code in orders.php: if (isset($_GET['cID'])) { // query not changed here } elseif (isset($_GET['status'])) { // this query not changed either } else { $orders_query_raw = "select o.approved, o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC"; } $orders_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $orders_query_raw, $orders_query_numrows); $orders_query = tep_db_query($orders_query_raw); New code in orders.php. $orders_query_raw is the query in question. I added $orders_query_count so that the pagination function would grab the total number of orders from there instead of from $orders_query_raw. if (isset($_GET['cID'])) { //not changed } elseif (isset($_GET['status'])) { // not changed } else { $orders_query_raw = "select o.approved, o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, y.orders_status_name, x.text as order_total from " . TABLE_ORDERS . " o left join (select * from " . TABLE_ORDERS_TOTAL . " ot where ot.class = 'ot_total') x on (o.orders_id = x.orders_id) left join (select * from " . TABLE_ORDERS_STATUS . " s where s.language_id = '" . (int)$languages_id . "' ) y on (o.orders_status = y.orders_status_id) order by o.orders_id DESC"; $orders_query_count="from orders"; } $orders_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $orders_query_raw, $orders_query_numrows, $orders_query_count); $orders_query = tep_db_query($orders_query_raw); In case helpful, this is the old code in includes/classes/split_page_results.php (this code calculates the number of pages to display for pagination, and adds the limit at the end of the raw query): function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) { if (empty($current_page_number)) $current_page_number = 1; $pos_to = strlen($sql_query); $pos_from = strpos($sql_query, ' from', 0); $pos_group_by = strpos($sql_query, ' group by', $pos_from); if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by; $pos_having = strpos($sql_query, ' having', $pos_from); if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having; $pos_order_by = strpos($sql_query, ' order by', $pos_from); if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by; $reviews_count_query = tep_db_query("select count(*) as total " . substr($sql_query, $pos_from, ($pos_to - $pos_from))); $reviews_count = tep_db_fetch_array($reviews_count_query); $query_num_rows = $reviews_count['total']; $num_pages = ceil($query_num_rows / $max_rows_per_page); if ($current_page_number > $num_pages) { $current_page_number = $num_pages; } $offset = ($max_rows_per_page * ($current_page_number - 1)); if ($query_num_rows > 0) $sql_query .= " limit " . $offset . ", " . $max_rows_per_page; } And new code in split_page_results.php: function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows, &$count_query='') { if (empty($current_page_number)) $current_page_number = 1; if (empty($count_query)) { $count_query = $sql_query; $pos_to = strlen($sql_query); $pos_from = strpos($sql_query, ' from', 0); $pos_group_by = strpos($sql_query, ' group by', $pos_from); if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by; $pos_having = strpos($sql_query, ' having', $pos_from); if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having; $pos_order_by = strpos($sql_query, ' order by', $pos_from); if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by; } else { $pos_to = strlen($count_query); $pos_from = 0; } $reviews_count_query = tep_db_query("select count(*) as total " . substr($count_query, $pos_from, ($pos_to - $pos_from))); $reviews_count = tep_db_fetch_array($reviews_count_query); $query_num_rows = $reviews_count['total']; $num_pages = ceil($query_num_rows / $max_rows_per_page); if ($current_page_number > $num_pages) { $current_page_number = $num_pages; } $offset = ($max_rows_per_page * ($current_page_number - 1)); if ($query_num_rows > 0) $sql_query .= " limit " . $offset . ", " . $max_rows_per_page; } Thanks for any help. First time posting here, so please let me know if there is anything I can add or change. Hopefully this is something that, once figured out, could help others with slow orders.php pages.
×