Jump to content

Noodleman

Member
  • Posts

    728
  • Joined

  • Last visited

  • Days Won

    28

Everything posted by Noodleman

  1. The updated plan is still showing that table I (CubeCart_inventory) is not making use of the primary key which is being called as part of the join "O.product_id = I.product_id" So, the question becomes why? Try running this query. how long does it take? SELECT Sum(O.quantity) AS quan, O.product_id, I.name FROM `CubeCart_order_inventory` AS O INNER JOIN `CubeCart_order_summary` AS S ON S.cart_order_id = O.cart_order_id INNER JOIN `CubeCart_inventory` AS I FORCE INDEX FOR JOIN (PRIMARY) ON O.product_id = I.product_id WHERE ( S.`status` = 2 OR S.`status` = 3 ) GROUP BY I.product_id DESC ORDER BY `quan` DESC LIMIT 15 offset 0 what does the plan of this SQL query look like?
  2. Thanks I spotted an issue, although this won't be the cause of your issue. CubeCart_order_inventory.product_id and CubeCart_inventory.product_id have a different default value. This will prevent the use of indexes being used when doing joins on this field. explain may tell you it's using it, but it lies. As a test, change CubeCart_order_inventory.product_id to have a default value of "None" ALTER TABLE `CubeCart_order_inventory` CHANGE `product_id` `product_id` INT(10) UNSIGNED NOT NULL; Also, there is an issue with CubeCart_order_inventory.cart_order_id which in your screen shot is showing a default value, but there shouldn't be one. Run this SQL code to align the field definitions: ALTER TABLE `CubeCart_order_inventory` CHANGE `cart_order_id` `cart_order_id` VARCHAR(18) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL; Then, run your query again with EXPLAIN before it and share the new plan. Also run the normal query.. how long does it take? 59,000 is minimal. If the indexes and columns are defined correctly it should take miliseconds. I've got queries against tables will several million records and they perform just fine. hmm, I did some further checking. I think I know what the issue actually is. would you be willing to export your order summary/inventory and inventory tables and send to me so I can import into my own DB server, sort it out and let you know the fix? the personal data can be updated/masked.
  3. Columns = the fields themselves. Rows = records within the table (the data)
  4. do you mean columns? If so, it will be additional fields added by modules and custom code over time. it's not unusual. Here is your SQL query formatted SELECT Sum(O.quantity) AS quan, O.product_id, I.name FROM `cubecart_order_inventory` AS O INNER JOIN `cubecart_order_summary` AS S ON S.cart_order_id = O.cart_order_id INNER JOIN `cubecart_inventory` AS I ON O.product_id = I.product_id WHERE ( S.`status` = 2 OR S.`status` = 3 ) GROUP BY I.product_id DESC ORDER BY `quan` DESC LIMIT 15 offset 0 We need to look closer at the join conditions and where clauses. Check the structure of each column. let me know what each is setup/defined as. (For example, INT(11) or VARCHAR). It might be easier to just copy/paste a screen shot of your table "Structure" from PhpMyadmin for each table. CubeCart_order_inventory.cart_order_id CubeCart_order_inventory.product_id CubeCart_order_summary.cart_order_id CubeCart_order_summary.status CubeCart_inventory.product_id
  5. Drop all indexes on the CubeCart_order_summary table then run this SQL query: ALTER TABLE `CubeCart_order_summary` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `cart_order_id` (`cart_order_id`), ADD KEY `customer_id` (`customer_id`), ADD KEY `status` (`status`), ADD KEY `email` (`email`), ADD KEY `order_date` (`order_date`); Drop all indexes on the CubeCart_inventory table then run this SQL query: ALTER TABLE `CubeCart_inventory` ADD PRIMARY KEY (`product_id`), ADD KEY `status` (`status`), ADD KEY `popularity` (`popularity`); ALTER TABLE `CubeCart_inventory` ADD FULLTEXT KEY `fulltext` (`product_code`,`description`,`name`); Drop all indexes on the CubeCart_order_inventory table then run this SQL query: ALTER TABLE `CubeCart_order_inventory` ADD PRIMARY KEY (`id`), ADD KEY `product_id` (`product_id`), ADD KEY `cart_order_id` (`cart_order_id`), ADD KEY `options_identifier` (`options_identifier`); this will force rebuild indexes and correct any issues/missing etc. If that doesn't help, let me know. There are some other possibilities.
  6. I think you are missing indexes on your order summary / order inventory table based on the explain plan. can you validate?
  7. Tax Exempt customers may cause you the problem in newwer versions of CubeCart and could be the root cause of the issue. Open & Edit the following file: /modules/plugins/tax_exempt_customers/language/module.definitions.xml replace the entire contents of the file with: <?xml version="1.0" encoding="UTF-8"?> <definitions version="1.0"> <string name="module_title"><![CDATA[Tax Exempt Customers]]></string> </definitions> The module doiesn't correctly make use of the language system (It will in the next version), however if you have an empty language file CubeCart seems to not like it and will break the entire language system. If this doesn't fix the issue, the next task will be to check EVERY module you have installed (regardless of status) has a valid module.definitions.xml language file.
  8. You are definately missing indexes. There should be at least "possible keys", but there are none in your plan. quickest way to fix/check manually would be to do a clean install of the latest version of CubeCart to a new DB, then do a side by side compare of each table indexes. create any that are missing. Chances are if you are missing one you are missing more elsewhere. in PhpMyAdmin, open the table, go to the "Structure" tab, then expand "Indexes". If you have indexes and still see those long query times there is something else going on and it may require some tuning. report your results back here. happy to help tune queries. I am a DB guy
  9. Don't trust the timings reported in debug move. run the SELECT queries manually and validate. I've had i t reported miliseconds before when in reality it's taking minutes. can you run the same queries with the word EXPLAIN before them. need both query run times (from phpmyadmin) and also the results of the EXPLAIN query. I am out of the office for the afternoon so my replies may be delayed.
  10. load the page in debug mode. Capture the SQL Queries being run. manually run each one in via PhpMyAdmin against your database to isolate those which are being a problem. The root cause will likely be the volume of data and a long running query causing a lock on a table. Once we know the query it may be able to be improved via additional indexes. use MySQL EXPLAIN to generate the execution plan for the problem SQL query, share both the query and plan
  11. did you ever look into what I suggested? what modules do you have installed? If you edit any installed language to change a language string, do you run into any issues (white screen etc).
  12. shipping is calculated at checkout based on rules from your shipping modules, so it's going to be a little tricky to show the differences at the product level, especially if your customer isn't logged in and made location data available. you could set my per product shipping module and set your current flat rate shipping module as the default. https://www.noodleman.co.uk/link-cubecart-products-to-specific-shipping-services.html Then, setup the all-in-one module with your rates for various destinations, and link those products which need to use those rules to the all-in-one module. It should all calculate correctly at checkout. There may be other solutions available, but this is the one I use for these type of scenarios.
  13. It does create a new one for you, as long as the new directory has the correct permissions. If you have made any modifications to your .htaccess file you should take a backup of it first so you can re-apply those changes to the newly generated file.
  14. you might need to delete the .htaccess file and let it re-create. ensure permissions are correct on the newly created directory.
  15. Morning Henry, My schedule is improving, but not yet clear. I am close to completing a large project which is consuming most of my time. once that is out of the way I have a collection of smaller items to work on for people, including this request for you. If I am lucky, I will complete the larger project by the end of next week and can start working on the smaller items in the queue in a couple of weeks.
  16. after taking a fresh look today I resolved this.
  17. I have a requirement to override option prices under some conditions. Let's assume we have a product "test product" with two options,. Option A = £0.00 Option B = £10.00 If the customer chooses option A & B I need to make the price of option B become £0.00. I've got the prices to behave in this way, but I am stuck with a problem related to tax. Even though the price is showing as 0 in the cart, the tax is still calculated on the original cost of the option. I've found no way to bypass this at the moment. It appears the tax calculation has been cobbled together using magic. Can anybody recommend how to stop the cart calculating the tax on an option price which I already set to 0?
  18. What tax rules do you have defined? What tax group is linked with the gift certificate? when cxhecking out as a customer, what country did your customer use for the billing/delivery address? do you tax by billing, or delivery address?
  19. If you run the test email process in store settings, does it work/arrive? If it does, then it's probably an issue with the mail provider relaying the emails rather than CC sending the emails.
  20. do you have english GB installed? If not, that's the issue.
  21. https://github.com/cubecart/v6/issues/857 Similar to this request, raised in 2015.
  22. I've got this on the roadmap for my Price List plugin, but not sure when it will get added at the moment.
  23. Morning Brian, Thanks for your time and thoughts. Q: What is your solution to making sure that abc gets re-associated with filter_conditions and blue gets re-associated with colour? A: I've already built a dynamic filter which uses logic to translate the URL parameters into filter conditions. It's complex but working well.The parameters themselves contain filter names & conditions which can be re-matched against the inventory and meta data. It's a module I want to release, but need to fix some other things with it first, like this SEO URL topic. The ability to understand the URL with parameterts and load a filtered caregory / drive menu etc already exists. To ensure unique combinations of URL parameters regardless of the order they appear in the URL I simply grab each parameter into an array (well, it's already one in $_GET). order the array keys by a-z, 0-9, serialize then MD5 hash. The hash can then be used to match against DB records to lookup the SEO URL which was auto generated. I also store the serialized url paramrters so I can rebuild the URL. Comment: Next, we need to choose a delimiter for these values. A: I don't think a delimiter is required due to the way I can store/lookup SEO url's based on the MD5 hash process i've mentioned above. md5 When trying to figure out if the URL is a dynamic filter or standard CC URL I am considering a check. validate if the SEO URL already exists in the CubeCart_seo_url table, if it does then it's a standard CC SEO URL. If it doesn't, assume it's a dynamic filter URL and apply the custom process to translate it to filter conditions.
  24. Sure, let me walk through a more specific example. I think I am being as clear as mud On my site I have a URL to a category: https://www.noodleman.co.uk/order-fulfilment.html The SEO URL being order-fulfilment.html. When you open this URL, CubeCart looks up order-fulfilment.html from the CubeCart_seo_url database table to get the category ID. In reality the URL contains parameters. Using a dump of $_GET those would be array(3) { ["_a"]=> string(8) "category" ["cat_id"]=> string(3) "51" } so, unmasked it's https://www.noodleman.co.uk/index.php?_a=category&cat_id=51 If you go to the URL: https://www.noodleman.co.uk/index.php?_a=category&cat_id=51 CubeCart looks up the SEO URL, then re-writes the URL back to order-fulfilment.html. I need to do something similar, making up a random example URL with additional parameters: https://www.noodleman.co.uk/index.php?_a=category&cat_id=51&filter_conditions=abc&colour=blue If that URL is accessed, I would want to re-write the URL to something more SEO frienly dynamically. in this case something like https://www.noodleman.co.uk/blue-order-fulfilment.html Note, it's still the same category ID, I just need custom SEO URL's based on additional URL parameters for that category. Alternatively, if you were to go to the URL https://www.noodleman.co.uk/blue-order-fulfilment.html I would need it to have those additional parameters in the URL https://www.noodleman.co.uk/index.php?_a=category&cat_id=51&filter_conditions=abc&colour=blue
  25. IonCube supports PHP 7, you just have to ensure the developer who released it has the latest encoding software
×
×
  • Create New...