0

We've recently had to upgrade to a VPS from shared hosting because we were getting timeouts on a vital SQL query running from a PHP script.

This PHP script runs hundreds of times a day at random occasions, many times concurrently.

The error I found out while manually testing the PHP script was with this line:

while($rs = mysqli_fetch_assoc($result) { . . .

It would occasionally get the (2013) “Lost connection to MySQL server during query”. error.

Here is the query in question:

SELECT DISTINCT
  Inventory.id,
  Inventory.SKU AS inventory_sku,
  Inventory.Quantity AS inventory_quantity,
  MasterSKU.SKU_1,
  MasterSKU.SKU_2,
  MasterSKU.SKU_3,
  MasterSKU.SKU_4,
  MasterSKU.SKU_5,
  MasterSKU.SKU_6,
  MasterSKU.SKU_7,
  MasterSKU.SKU_8,
  MasterSKU.SKU_9,
  MasterSKU.SKU_10,
  MasterSKU.SKU_11,
  MasterSKU.SKU_12,
  MasterSKU.SKU_13,
  MasterSKU.SKU_14,
  MasterSKU.SKU_15,
  MasterSKU.SKU_16,
  MasterSKU.SKU_17,
  MasterSKU.SKU_18,
  MasterSKU.SKU_19,
  MasterSKU.SKU_20,
  MasterSKU.MultSKU,
  MasterSKU.QtySKU,
  MasterSKU.AltSKU,
  MasterSKU.SKU,
      '$qty' AS sold_quantity,
      '$sku' AS sold_sku
FROM eBayOrders
  LEFT OUTER JOIN MasterSKU
    ON MasterSKU.SKU = '$sku'
  LEFT OUTER JOIN Inventory
    ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20);

On localhost testing, a single instance of this query was running anywhere from 5 - 6.5 seconds.

I was able to optimize this a little bit and get it around to 4 seconds by taking away DISTINCT and adding GROUP BY

SELECT
  Inventory.id,
  Inventory.SKU AS inventory_sku,
  Inventory.Quantity AS inventory_quantity,
  MasterSKU.SKU_1,
  MasterSKU.SKU_2,
  MasterSKU.SKU_3,
  MasterSKU.SKU_4,
  MasterSKU.SKU_5,
  MasterSKU.SKU_6,
  MasterSKU.SKU_7,
  MasterSKU.SKU_8,
  MasterSKU.SKU_9,
  MasterSKU.SKU_10,
  MasterSKU.SKU_11,
  MasterSKU.SKU_12,
  MasterSKU.SKU_13,
  MasterSKU.SKU_14,
  MasterSKU.SKU_15,
  MasterSKU.SKU_16,
  MasterSKU.SKU_17,
  MasterSKU.SKU_18,
  MasterSKU.SKU_19,
  MasterSKU.SKU_20,
  MasterSKU.MultSKU,
  MasterSKU.QtySKU,
  MasterSKU.AltSKU,
  MasterSKU.SKU,
      '$quantitypurchased' AS sold_quantity,
      '$sku' AS sold_sku
FROM eBayOrders
  LEFT OUTER JOIN MasterSKU
    ON MasterSKU.SKU = '$sku'
  LEFT OUTER JOIN Inventory
    ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20)
GROUP BY Inventory.id;

With this I was able to get it down to around 4 seconds (again, for a single instance... while running concurrently on our shared hosting it was giving a timing out error after 30 seconds)

Now, with Shared Hosting, we were not able to change our MySQL configurations such as wait_timeout net_read_timeout and net_write_timeout.

My idea is to change these configurations to a near maximum number, and also enable the MySQL Slow Query Log to get log files.

Additionally, a thought came into my head to a processed tinyint column to my table and check for a successful UPDATE which is at the while($rs = mysqli_fetch_assoc($result), and if it gets this far, look for a successful query and add another query to UPDATE processed = 1. This way I can make sure the entire script has run till its end, and reference this in my database.

I've been working on this project for several months, and these timeout issues are killing the system so need to be fixed.

We are in process of doing the migration to VPS right now, I am wondering if anyone can think of a further way to optimize the query or tables, or any mysql configuration settings we can set to ensure my script and queries run 100% of the time.

Tables in question:

: MasterSKU

CREATE TABLE `MasterSKU` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SKU` varchar(255) NOT NULL,
 `AltSKU` varchar(255) DEFAULT NULL,
 `QtySKU` int(11) NOT NULL DEFAULT '1',
 `MultSKU` tinyint(1) NOT NULL DEFAULT '0',
 `SKU_1` varchar(255) DEFAULT NULL,
 `SKU_2` varchar(255) DEFAULT NULL,
 `SKU_3` varchar(255) DEFAULT NULL,
 `SKU_4` varchar(255) DEFAULT NULL,
 `SKU_5` varchar(255) DEFAULT NULL,
 `SKU_6` varchar(255) DEFAULT NULL,
 `SKU_7` varchar(255) DEFAULT NULL,
 `SKU_8` varchar(255) DEFAULT NULL,
 `SKU_9` varchar(255) DEFAULT NULL,
 `SKU_10` varchar(255) DEFAULT NULL,
 `SKU_11` varchar(255) DEFAULT NULL,
 `SKU_12` varchar(255) DEFAULT NULL,
 `SKU_13` varchar(255) DEFAULT NULL,
 `SKU_14` varchar(255) DEFAULT NULL,
 `SKU_15` varchar(255) DEFAULT NULL,
 `SKU_16` varchar(255) DEFAULT NULL,
 `SKU_17` varchar(255) DEFAULT NULL,
 `SKU_18` varchar(255) DEFAULT NULL,
 `SKU_19` varchar(255) DEFAULT NULL,
 `SKU_20` varchar(255) DEFAULT NULL,
 `processed` tinyint(1) NOT NULL DEFAULT '0',
 `comments` text,
 PRIMARY KEY (`id`),
 UNIQUE KEY `SKU` (`SKU`)
) ENGINE=MyISAM AUTO_INCREMENT=7815 DEFAULT CHARSET=latin1

: Inventory

CREATE TABLE `Inventory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SKU` varchar(255) NOT NULL,
 `Quantity` int(11) NOT NULL DEFAULT '0',
 `soldout` tinyint(1) NOT NULL DEFAULT '0',
 `reorderamt` int(11) NOT NULL DEFAULT '0',
 `zeroday` int(11) DEFAULT NULL,
 `category` text,
 `supplier` text,
 `channel` text,
 `location` text,
 `incoming` varchar(255) DEFAULT NULL,
 `lastmodified` timestamp NULL DEFAULT NULL,
 `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `SKU` (`SKU`)
) ENGINE=InnoDB AUTO_INCREMENT=4753 DEFAULT CHARSET=latin1

: eBayOrders

CREATE TABLE `eBayOrders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `OrderLineItemID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `SalesRecordNumber` int(11) DEFAULT NULL,
 `BuyerUserID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `BuyerEmail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `Title` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `SKU` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
 `Quantity` int(11) NOT NULL,
 `TransactionPrice` decimal(6,2) DEFAULT NULL,
 `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ExtendedOrderID` (`OrderLineItemID`)
) ENGINE=MyISAM AUTO_INCREMENT=11668 DEFAULT CHARSET=latin1
1

Do not splay an array across columns. Instead have another table to JOIN to, with up to 24 rows of SKUs.

In doing so, this un-optimizable clause goes away:

Inventory.SKU IN ( ... 24 columns of `MasterSKU` ... )

In its place will be a simple JOIN that can be optimized.

If you need more help, provide SHOW CREATE TABLE for the tables in question.

More

Start with a table something like:

CREATE TABLE OtherSkus (
    MasterSku VARCHAR(20) CHARSET=latin1 NOT NULL, -- for JOINing
    AltSku VARCHAR(20) CHARSET=latin1 NOT NULL,
    PRIMARY KEY(MasterSku, AltSku)
) ENGINE=InnoDB;

Then see how you can shrink the 24 SKU columns down to perhaps only 1.

  • Really appreciate the advice. This was/is an ambitious first real SQL project, so I am sure things are not entirely optimized properly. I added the SHOW CREATE TABLE results in the original question. The logic behind the MasterSKU table is a QtySKU which reflects the Quantity to be deducted for the SKU. MultSKU is a tinyint 0 or 1. If 1, the PHP script knows the SKU has Multiple SKU's associated with it, up to 20 SKU_1 - SKU_20. If 0 then there is just one SKU associated with it, and it looks only to AltSKU. – bbruman Mar 21 '17 at 22:05
  • The SKU column has no reference to the actual SKU, just taken from the eBay API Listings SKU. SKU is just a reference SKU to get the actual SKU/SKUs associated with the eBay listing (as I said previously, either in AltSKU or SKU_1 all the way up to a possible SKU_20 – bbruman Mar 21 '17 at 22:10
  • "Up to" -- all the more reason to put them in another table, not in columns. – Rick James Mar 22 '17 at 1:52
  • Surely SKU is always shorter than 255? Recommend you find some civilized limit. – Rick James Mar 22 '17 at 1:53
  • Please use InnoDB, not MyISAM. The latter is going away. – Rick James Mar 22 '17 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.