Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Optimizing db query to speed up orders.php


immortaldiamond

Recommended Posts

 

 

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. :ohmy: 

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.

Link to comment
Share on other sites

@immortaldiamond

Howdy,

While I can't answer your exact question, I have one for you...

osC 2.2-MS2 came out between 11 and 14 years ago. In software terms, it is absolutely ancient! Plus, with all of your efforts to make it PHP 7 ready, and that you have "very modified" it, you have essentially creating your own fork of osC. So, you're basically past the point that a osC person can support you any more. Perhaps in a MySQL forum, you may be able to get help to fine tune your query.

That said, it's time to consider upgrading to the latest Community Edition of osC.

The 'Community Edition' (also called osC-CE (for Community Edition), osC-BS (for Bootstrap, the platform it is based on), osC-Edge, osC-Final, and/or osC-Frozen. These are all different names for the same thing) is a big improvement over even the last 'official' v2.3.4 release in a number of ways:

1) It is responsive. This means that it will adjust the screen layout depending on the size of the screen of your customer's device. This is very important in this day of mobile devices. The 'official' version is not responsive.

2) It will work with the newer version of PHP, the scripting language used in osC. As more and more hosts upgrade their servers to PHP 7.x, older versions of osC will crash.

3) It is much more modular. This means that you can turn features on or off, or even add new features, all without touching the core code.

Unfortunately, it's not an in-place upgrade. You'd have to install it in it's own directory (with it's own database), migrate a COPY of the original database into the new store (assuming you want to keep your old history), move your product images over, and apply any new style changes and/or add-on additions you want.

You can find a link to the latest version of the Community Edition in my signature below.

Malcolm

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

It's quite true that much of even Edge/CE/Frozen's internals (other than User Interface and PHP-related upgrades) are not much different than even 2.2 MS2, and any SQL optimizations that can be applied almost across the entire version line. That said, I must agree with Malcolm and question the value of putting so much effort into an ancient code base (including upgrading to PHP 7), rather than starting your optimizations with the current latest and greatest. The expression "throwing good money after bad" comes to mind. I hope you're not investing any effort in making the store responsive, on top of everything else. Clinging to a very old release simply because you've invested so much in it to date may be false economy, if you step back and look at it coldly and rationally. However, to each his own...

If you are actually experiencing a database deadlock, that would be quite serious. As I don't recall ever hearing about anyone else having such problems (slowness, yes; deadlock, no), I would suspect that either some other change you've made along the way is causing the problem, or worse, there is a bug in the MySQL version you're using. Note also that the current (Edge) osC uses MySQLi as its interface.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

the left join can just be join i think, totals and status always being populated.

perhaps updating statistics for the table might help and possible another index. I will have a look at my site to see how it is different from the differen stock versions ...

Sorry guys but you are hammering too much ! this is a screw 😎

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

@immortaldiamond, innodb or isam table? It makes a big difference. What's locking it? It takes two.

Do an explain or desc on the queries (takes two, remember), some index's missing. MySQL should lock indexes and not table and, hopefully, gap vs. non-gap lock...

I would go to innodb. A lot more flexible for transactional tables.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Yea, something still not very right about your query. You were doing a full table scan before. 110k record really isn't that much. Your probably doing few too many unnecessary order by desc stuff even now. Remember orders table has a primary key order_id, go from there and inner join the orders_total and orders_status should be good enough, and add indexes to the sub tables if necessary. You want your explain type to be ref as much as possible, and use keys. As with the group by, there's not much you can do, it will use temp space file sort, unless u have boat loads of RAM and have configured the engine to do so. Go to innodb, it will give you a lot more flexibility to tune ur stuffs...btw, you have to look at engine status to see dead lock, and not explain...FYI.

Link to comment
Share on other sites

23 hours ago, immortaldiamond said:

mostly just changing  mysql to mysqli.

That's actually a very small part of the upgrade to PHP 7. It's not even really a PHP issue, just that MySQL is no longer supported and MySQLi is the up to date library. Anyway, there are probably numerous PHP hits you're going to come across over time, if you haven't been lucky finding them already. More work for you.

A word of advice on changing to Woocommerce, Magento, or some other shop. These desires to change are frequently driven by consultants, coworkers, bosses, and others who extol PlatformX as the greatest thing since sliced bread, when in truth they like it because they're most familiar with it. You're going to have the same hill to climb becoming familiar with it as you did with osC, so don't discard osC on promises that PlatformX will be absolutely effortless -- it won't be. Go in with your eyes wide open.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

always use inner joins unles you really are in a situation that the joining table does not have matching records, it makes a bbiigg difference.

For orders, they will have matching order total and status records, so no need to use an outer join.

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Quote

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.

I'm sorry to hear that you spent so much time and effort to upgrade to PHP 7, when the work was already done for you in Edge/CE. It sounds like you probably hit most everything -- from your previous post it sounded like you had done little beyond changing to MySQLi, which as I said is a small part of it. Offhand I can't think of any place else to work on (for PHP 7), but even Edge/CE is occasionally finding something that Gary missed.

Regarding the lack of osC pros, and its declining use, it seems that any platform can be the darling of ecommerce, and within a year or two it's in steep decline (for example, Magento). I think you're going to run into that problem with any platform, so once you're established on one you might want to stick with it rather than jumping to the latest crowd favorite. Maybe even you can become a professional familiar with it!

Good developers keep written notes on every change they make, including add-ons installed, so that they can be replicated on an upgrade or even a switch to a new platform. It sounds like unfortunately your system is now a black box, where you really don't know what's going on inside it. All I can suggest is that you install an Edge/CE test store, migrate a copy of your data to it, and see what function you seem to be missing. It will take some slogging, but with luck and perseverance you can figure out what add-ons are needed, what custom coding is needed (where those notes would have come in handy), and what modules simply need to be turned on and configured (former add-ons now built in). I'll bet it will still be less work than moving to an entirely new platform. Many add-ons in the osC library have been reported to have been made to run on Edge/CE, without major pain, unless they're heavily User Interface. You won't know until you look at it. In the end, you have the latest and greatest (for osC) with PHP 7.2 compatibility, mobile friendly (responsive), and lots of feature updates. You will be well positioned for whatever comes after, be it 2.4 or a project fork based on 2.4 or 3.0.

Presumably it wasn't your fault, but your company let its copy of osC fall way too far behind. You can't do that with software -- the underlying PHP etc. is constantly being upgraded, and will eventually break your site. It's hard to persuade people to keep up to date with new releases, until they find themselves in a pickle with an ancient version that will no longer run. The same applies to any other ecommerce package -- they need to be frequently updated to stay current. Admittedly, osC, with its requirements for heavy code editing for add-ons, has discouraged keeping current, but Edge/CE is much better behaved in this area.

Link to comment
Share on other sites

I think apology is not really needed, it is the beauty of open source and that you are free to do whatever you like...

Well, you may want to review the join types for SQL. Inner join is very straight forward, you just get whenever 1 = 1 (true) kinda deal. On the other hand, for the left join, I always try to imagine it as the left side of the scale being lifted up, and that everything form the left side is gonna slide out even when 1 != 1. It is often that you would have to apply a NVL or nullif or ifnull type function to the right side to make your data useful. So to decided if a left join is necessary, ask yourself do you need everything from the left side?

As for group by clause, in general it is only needed whenever you use aggregate function (sum, max, min, and etc) in the column; otherwise, I would skip that, too.

Ya, your explain plan looks good. Yes, no need to worry about the derived table as the optimizer will take care of that, and it's only 20 rows...

Lastly, for the innodb, I think I have done it in the table level before. It's a simple job, but I would test it in a dev db first. With 100K records, and if you're resource bounded on a shared host, a bunch of DMLs mixed with a very long query will for sure give your isam table a deadlock. With the innodb, in this case, the transaction isolation level and index gap lock will become very handy for sure...

 

7 hours ago, immortaldiamond said:

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?

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...