Noodleman

since it's not mechanical you don't have the seek time to find the sector, much quicker IO (recommend M.2) the only concern with your setup is the loss of power to the storage and it not being unmounted correctly. based onthe storage limits, why not just get a 256GB USB stick so it's self powered?

You might find it better to use a large USB drive instead of an SSD. The hub itself could be a factor. When the power drops to the hub (car restart etc) it's the same as unplugging the USB device, and plugging it back in again. This is likely causing your table to scan the media on the storage device as it's plugged in and is the main factor of the delay. if that statement is true when the NTFS drive is full you should see similar behaviour. you really want a storage device that's always on. because the drive isn't being "safely" powered off/unplugged it may be marking the file system on the drive as unclean, so it's doing a file system check when it powers back on. this happens a lot on FAT systems, but it also happens on NTFS. NTFS really doesn't like being unplugged in this way and typically you may run into more corruptions /issues due to it over time

NTFS is a more advanced file system, it stores it's indexes in a different method to FAT based systems. f.y.i, you can manage partitions in windows using "Disk Manager", inclusing hidden partitions. assuming windows knows how to read the file system on the HDD. It can probably read this file system faster because it's more advanced / indexed better. The real test would be to make the SSD NTFS and validate the issue isn't something firmware related with the SSD itself. you should just be able to adjust the mount command being used for your media to the specific partition you want to use, the hidden one should be irrelevent in that context. In a table, when the SD/HDD is plugged in it does a scan of the drive. this takes a little time. it could be this that you are running into issues with. Assuming your HDD is powered from the tablet itself then it shouldn't need to cycle.

Yes... What's the question?

That sounds more appropriate. I think it's an excellent idea.

Here's the breakdown of the issue. Indexes are being used and are improving performance to the level you are reporting. without them it would be much worse. The SQL query we are focused on derives the quantity for each product, then sorts by that quantity. this is an expensive query. There isn't much that can be done to improve this SQL query. @Al Brookbanks, this may be a candidate for an improvement. The queries probably shouldn't run on page load in this case, but perhaps on demand via a "refresh stats" button on the statistics page. This can then run the queries, gather the results from each and store the results in a database statistics table. the reports can then pull the derived values from the table rather than calculate it every time. This isn't too much of an issue as it's only going to impact stores with a large data set.

You were in the right place for the queries. would you be willing to let me take a look at it directly?

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?

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.

Columns = the fields themselves. Rows = records within the table (the data)

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

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.

I think you are missing indexes on your order summary / order inventory table based on the explain plan. can you validate?

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.

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