Jump to content

Noodleman

Member
 View Profile  See their activity

  • Posts

    728

  • Joined

  • Last visited

  • Days Won

    28

 Content Type 

Posts posted by Noodleman

    Statistics grinds site to a halt

    in Technical Help

    Posted · Edited by Noodleman

    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?

    Statistics grinds site to a halt

    in Technical Help

    Posted

    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?

     

     

    5 minutes ago, keat said:

    Are we 100% vertain that this isn't just related to sheer volume of data. Order summary id is showing 59,683, so potentially 59,000 orders to trawl through.

    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.

    Statistics grinds site to a halt

    in Technical Help

    Posted · Edited by Noodleman

    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

     

     

    Statistics grinds site to a halt

    in Technical Help

    Posted · Edited by Noodleman

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

    Billing Address - no State

    in Technical Help

    Posted

    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.

    Statistics grinds site to a halt

    in Technical Help

    Posted

    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 :D

    Statistics grinds site to a halt

    in Technical Help

    Posted

    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.

     

    Statistics grinds site to a halt

    in Technical Help

    Posted

    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 :)

     

     

    Multiple prices for same item

    in Technical Help

    Posted

    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.

    Shipping extension

    in Technical Help

    Posted

    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.

     

    [Resolved] Override option prices.. how?

    in Developer Forum

    Posted

    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?

     

    Dymanic, custom SEO URL - what's the best method to use?

    in Developer Forum

    Posted

    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.

     

    Dymanic, custom SEO URL - what's the best method to use?

    in Developer Forum

    Posted

    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

     

     

     

     

    CC6 + PHP7

    in Technical Help

    Posted

    44 minutes ago, Al Brookbanks said:

    It will be fine unless you have extensions that require Ioncube. 

    IonCube supports PHP 7, you just have to ensure the developer who released it has the latest encoding software ;)

×
×
  • Create New...