Post by head_dunce » Thu Apr 25, 2019 10:05 am

So in playing with the sorting of subcategories, I'm seeing that there are the same involved db queries executed multiple times on a page. It seems a category page loads all category/subcategory information 3 times. I assume that's once for the top menu, once for the side menu, and once for the information on the page. Why isn't this loaded once and passed around in a data structure? No wonder why it's so slow.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 10:40 am

You will find that many parts of OC are written with developer (dare I say... yes I do) laziness in mind as opposed to efficiency and effectiveness.
Some of it reeks of trail and error development and vital parts are sheer after-thoughts like seo urls and multi-language & mb-character-set support.

Then again, it is free of charge so, yes by all means vent but then move on, adapt and optimize yourself (if you can) and work with that.

I already knew after installing V 3.0.2.0 that I would not seek any upgrade in the future, my main reason for not using the OCMOD facility, just adapt the core manually. I take the fixes from new releases one by one and implement them manually if I think those fixes are required.
A lot of manual work and prone to error but at least that way I know what is being executed, where and why.

And from what I have seen, many modifications you definitely do not want to blindly "OCMOD install", they are seldom "fire and forget" quality, more "fire and may your god help you".

Of course, I do keep a very detailed log of that I am doing, when and where.

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by IP_CAM » Thu Apr 25, 2019 11:23 am

Of course, I do keep a very detailed log of that I am doing, when and where.
Well, in contrary to me, I just fill up my Disk with complete Installs Backups,
every other day, and then forget, to have installed something, meaningly
'new', a good while ago already in one of the older TestVersion Code... :crazy:
From that point of view, I should better have concentrated on a later Version,
with less of everything, existing for older Versions, on Ideas, Infos + Code.

It took Years, to only find out, partly, at least ... 8)
But I would not have to, so, what the heck !? :D
Ernie

I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free OpenCart LIGHT v.1.5.6.5 Software Edition
can be seen in Action here: http://www.jti.li/shop/
---
940+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by head_dunce » Thu Apr 25, 2019 7:22 pm

This is disappointing. I'm confused at all the versions then, is there somewhere that explains the differences between the versions?

So since Query Cache is no longer supported
https://mysqlserverteam.com/mysql-8-0-r ... ery-cache/
Is anyone using ProxySQL?
https://proxysql.com

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 7:32 pm

before you start the cache route, better look at DB indexes first as OC out of the box has no DB optimization whatsoever.
Caching will camouflage the absence of optimization and should always be the last line of defense.

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by head_dunce » Thu Apr 25, 2019 7:42 pm

Wow, ok, thanks for the tip. I saw some post a long time ago about which indexes to create for version 1, anything change for version 3?

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 7:54 pm

well, I am not privy to previous versions, I started at 3.0.2.0 and will end there.

I simply list out all queries which are performed and make a list of all the columns used in the where clauses or on clauses for joins as well as the sorting.
Those are the columns I put indexes on if they are not part of the primary keys.

then you can use microtime to see if those queries improved and use explain to verify which index is used by mysql.
It takes some effort but it is a huge improvement over the default (non) setting.

after that I implemented individual caching for queries I still found to be wanting like the product totals for the filter module, I use the filters much more than categories and I need to count the products per filter, not to show the product count but because I do not show empty filters and empty filter groups.

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by IP_CAM » Thu Apr 25, 2019 9:45 pm

I saw some post a long time ago about which indexes to
create for version 1, anything change for version 3?
Well, famous ATOMIX full DB Indexer/InnoDB Changer file should still work
for OC-3, I assume:
https://github.com/lilalaunesau/opencar ... /turbo.php

I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free OpenCart LIGHT v.1.5.6.5 Software Edition
can be seen in Action here: http://www.jti.li/shop/
---
940+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by letxobnav » Thu Apr 25, 2019 10:06 pm

true, that script is also valid for V3 but as I mentioned before, it is not enough as that script only considers the columns it can find with "_id" in the name.
There are many more columns without "_id" in the name to consider.

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by head_dunce » Thu Apr 25, 2019 10:07 pm

Looks like there are some indexes that come with the basic install -

Code: Select all

SELECT TABLE_NAME,COUNT(1) index_count,GROUP_CONCAT(DISTINCT(index_name) SEPARATOR ',\n ') indexes FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'opencart' AND INDEX_NAME != 'primary' GROUP BY TABLE_NAME ORDER BY COUNT(1) DESC ;
+----------------------------------------+-------------+----------------------+
| TABLE_NAME                             | index_count | indexes              |
+----------------------------------------+-------------+----------------------+
| oc_cart                                |           5 | cart_id              |
| oc_category_to_google_product_category |           2 | category_id_store_id |
| oc_customer_login                      |           2 | email, ip            |
| oc_product_advertise_google            |           2 | product_id_store_id  |
| oc_seo_url                             |           2 | keyword, query       |
| oc_address                             |           1 | customer_id          |
| oc_advertise_google_target             |           1 | store_id             |
| oc_category                            |           1 | parent_id            |
| oc_category_description                |           1 | name                 |
| oc_customer_ip                         |           1 | ip                   |
| oc_language                            |           1 | name                 |
| oc_location                            |           1 | name                 |
| oc_order_product                       |           1 | order_id             |
| oc_order_total                         |           1 | order_id             |
| oc_product_description                 |           1 | name                 |
| oc_product_discount                    |           1 | product_id           |
| oc_product_image                       |           1 | product_id           |
| oc_product_special                     |           1 | product_id           |
| oc_product_to_category                 |           1 | category_id          |
| oc_review                              |           1 | product_id           |
+----------------------------------------+-------------+----------------------+

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Thu Apr 25, 2019 10:28 pm

Trimmed some of the comment lines so it would fit into a post here, this is the stock install indexes. Easier to view if you copy the code and paste it into a local text editor so the lines line up

Code: Select all

MariaDB [opencart]> SHOW INDEX FROM oc_cart FROM opencart;
| Table   | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_cart |          0 | PRIMARY  |            1 | cart_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cart |          1 | cart_id  |            1 | api_id       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cart |          1 | cart_id  |            2 | customer_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cart |          1 | cart_id  |            3 | session_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cart |          1 | cart_id  |            4 | product_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_cart |          1 | cart_id  |            5 | recurring_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_category_to_google_product_category FROM opencart;
| Table                                  | Non_unique | Key_name             | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------------------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_category_to_google_product_category |          0 | PRIMARY              |            1 | google_product_category | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_category_to_google_product_category |          0 | PRIMARY              |            2 | store_id                | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_category_to_google_product_category |          1 | category_id_store_id |            1 | category_id             | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_category_to_google_product_category |          1 | category_id_store_id |            2 | store_id                | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_customer_login   FROM opencart;
| Table             | Non_unique | Key_name | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_customer_login |          0 | PRIMARY  |            1 | customer_login_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_customer_login |          1 | email    |            1 | email             | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_customer_login |          1 | ip       |            1 | ip                | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_advertise_google FROM opencart;
| Table                       | Non_unique | Key_name            | Seq_in_index | Column_name                 | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------------+------------+---------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_advertise_google |          0 | PRIMARY             |            1 | product_advertise_google_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_advertise_google |          0 | product_id_store_id |            1 | product_id                  | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_product_advertise_google |          0 | product_id_store_id |            2 | store_id                    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_seo_url FROM opencart;
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_seo_url |          0 | PRIMARY  |            1 | seo_url_id  | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| oc_seo_url |          1 | query    |            1 | query       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_seo_url |          1 | keyword  |            1 | keyword     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_address FROM opencart;
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_address |          0 | PRIMARY     |            1 | address_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_address |          1 | customer_id |            1 | customer_id | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [opencart]> SHOW INDEX FROM oc_advertise_google_target FROM opencart;
| Table                      | Non_unique | Key_name | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------+------------+----------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_advertise_google_target |          0 | PRIMARY  |            1 | advertise_google_target_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_advertise_google_target |          1 | store_id |            1 | store_id                   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_category FROM opencart;
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_category |          0 | PRIMARY   |            1 | category_id | A         |         199 |     NULL | NULL   |      | BTREE      |         |               |
| oc_category |          1 | parent_id |            1 | parent_id   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_category_description FROM opencart;
| Table                   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_category_description |          0 | PRIMARY  |            1 | category_id | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_category_description |          0 | PRIMARY  |            2 | language_id | A         |         199 |     NULL | NULL   |      | BTREE      |         |               |
| oc_category_description |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_customer_ip  FROM opencart;
| Table          | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_customer_ip |          0 | PRIMARY  |            1 | customer_ip_id | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| oc_customer_ip |          1 | ip       |            1 | ip             | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_language  FROM opencart;
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_language |          0 | PRIMARY  |            1 | language_id | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| oc_language |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_location FROM opencart;
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_location |          0 | PRIMARY  |            1 | location_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_location |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_order_product FROM opencart;
| Table            | Non_unique | Key_name | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_order_product |          0 | PRIMARY  |            1 | order_product_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_order_product |          1 | order_id |            1 | order_id         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_order_total FROM opencart;
| Table          | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_order_total |          0 | PRIMARY  |            1 | order_total_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_order_total |          1 | order_id |            1 | order_id       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_description FROM opencart;
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_description |          0 | PRIMARY  |            1 | product_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_description |          0 | PRIMARY  |            2 | language_id | A         |        5802 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_description |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_discount FROM opencart;
| Table               | Non_unique | Key_name   | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_discount |          0 | PRIMARY    |            1 | product_discount_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_discount |          1 | product_id |            1 | product_id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_image FROM opencart;
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_image |          0 | PRIMARY    |            1 | product_image_id | A         |        4551 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_image |          1 | product_id |            1 | product_id       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_special FROM opencart;
| Table              | Non_unique | Key_name   | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_special |          0 | PRIMARY    |            1 | product_special_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_special |          1 | product_id |            1 | product_id         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_product_to_category FROM opencart;
| Table                  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_product_to_category |          0 | PRIMARY     |            1 | product_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_to_category |          0 | PRIMARY     |            2 | category_id | A         |        6120 |     NULL | NULL   |      | BTREE      |         |               |
| oc_product_to_category |          1 | category_id |            1 | category_id | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |

MariaDB [opencart]> SHOW INDEX FROM oc_review FROM opencart;
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_review |          0 | PRIMARY    |            1 | review_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| oc_review |          1 | product_id |            1 | product_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |


Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Thu Apr 25, 2019 10:47 pm

Disabling the product count made a HUGE difference, saw that suggestion here -
viewtopic.php?t=200411

Not sure what indexes to set up, seems there are a bunch already so someone thought about it in Version 3.0.3.1

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 10:57 pm

right, there are indexes and I think you are using the latest version. But do you see any index on the product table?

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by head_dunce » Thu Apr 25, 2019 11:08 pm

Doesn't look like it, just the product_id which is already the primary key. But looking at the table, not sure there's anything else that should be indexed?

Code: Select all

MariaDB [opencart]> show index from oc_product from opencart \G;
*************************** 1. row ***************************
        Table: oc_product
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: product_id
    Collation: A
  Cardinality: 5802
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

Code: Select all

MariaDB [opencart]> describe oc_product;
+-----------------+---------------+------+-----+------------+----------------+
| Field           | Type          | Null | Key | Default    | Extra          |
+-----------------+---------------+------+-----+------------+----------------+
| product_id      | int(11)       | NO   | PRI | NULL       | auto_increment |
| model           | varchar(64)   | NO   |     | NULL       |                |
| sku             | varchar(64)   | NO   |     | NULL       |                |
| upc             | varchar(12)   | NO   |     | NULL       |                |
| ean             | varchar(14)   | NO   |     | NULL       |                |
| jan             | varchar(13)   | NO   |     | NULL       |                |
| isbn            | varchar(17)   | NO   |     | NULL       |                |
| mpn             | varchar(64)   | NO   |     | NULL       |                |
| location        | varchar(128)  | NO   |     | NULL       |                |
| quantity        | int(4)        | NO   |     | 0          |                |
| stock_status_id | int(11)       | NO   |     | NULL       |                |
| image           | varchar(255)  | YES  |     | NULL       |                |
| manufacturer_id | int(11)       | NO   |     | NULL       |                |
| shipping        | tinyint(1)    | NO   |     | 1          |                |
| price           | decimal(15,4) | NO   |     | 0.0000     |                |
| points          | int(8)        | NO   |     | 0          |                |
| tax_class_id    | int(11)       | NO   |     | NULL       |                |
| date_available  | date          | NO   |     | 0000-00-00 |                |
| weight          | decimal(15,8) | NO   |     | 0.00000000 |                |
| weight_class_id | int(11)       | NO   |     | 0          |                |
| length          | decimal(15,8) | NO   |     | 0.00000000 |                |
| width           | decimal(15,8) | NO   |     | 0.00000000 |                |
| height          | decimal(15,8) | NO   |     | 0.00000000 |                |
| length_class_id | int(11)       | NO   |     | 0          |                |
| subtract        | tinyint(1)    | NO   |     | 1          |                |
| minimum         | int(11)       | NO   |     | 1          |                |
| sort_order      | int(11)       | NO   |     | 0          |                |
| status          | tinyint(1)    | NO   |     | 0          |                |
| viewed          | int(5)        | NO   |     | 0          |                |
| date_added      | datetime      | NO   |     | NULL       |                |
| date_modified   | datetime      | NO   |     | NULL       |                |
+-----------------+---------------+------+-----+------------+----------------+

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 11:22 pm

sure, everything with "_id" in the name, status, the dates, sort_order, model, price.

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by head_dunce » Thu Apr 25, 2019 11:30 pm

letxobnav wrote:
Thu Apr 25, 2019 11:22 pm
sure, everything with "_id" in the name, status, the dates, sort_order, model, price.
I can understand name and price since the page can be sorted by those. I think some of the others only will apply to the admin page though. Thanks for the tip.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Thu Apr 25, 2019 11:50 pm

some fragments of the products query:

Code: Select all

		$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";





		
		$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
		





			if (!empty($data['filter_name'])) {
				$sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
			}



	
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} elseif ($data['sort'] == 'p.price') {
				$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}

Crystal Light Centrum Taiwan
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by IP_CAM » Fri Apr 26, 2019 2:09 am

Well, sorry for the Link repetition, but whatever you mean about it,
as long as no better free Alternative exists, by just simply pressing two
Buttons, those like me must use, what already exists! :D
Ernie
Two more fine tools to speed up OC v.3:
WeismannWeb Category Cache:
https://gist.github.com/weismannweb/8ad ... 8d751281b2
Database Cache:
https://www.opencart.com/index.php?rout ... n_id=35767

I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free OpenCart LIGHT v.1.5.6.5 Software Edition
can be seen in Action here: http://www.jti.li/shop/
---
940+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 8 guests