Jump to content
beyzad

Please help to optimize this query

Recommended Posts

Posted (edited)

Hello guys.

Please do me a favor and help me solve this slow query

Tables:

CREATE TABLE `products` (
  `ProductId` int(11) NOT NULL AUTO_INCREMENT,
  `CategoryId` int(11) NOT NULL,
  `BrandId` int(11) DEFAULT NULL,
  `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `PName` varchar(255) COLLATE utf8_persian_ci NOT NULL,
  `UnitId` int(11) DEFAULT NULL,
  `PNameEn` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL,
  `PShortName` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL,
  `PDescription` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL,
  `Weight` int(11) DEFAULT NULL,
  `PImage` varchar(100) COLLATE utf8_persian_ci NOT NULL DEFAULT 'noimage.png',
  `PIcon` varchar(100) COLLATE utf8_persian_ci DEFAULT 'noicon.png',
  `PGallery` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL,
  `PDetail` text COLLATE utf8_persian_ci,
  `PTags` text COLLATE utf8_persian_ci,
  `PSpecial` tinyint(4) NOT NULL DEFAULT '0',
  `PView` int(11) NOT NULL DEFAULT '0',
  `Status` tinyint(2) DEFAULT '1' COMMENT '0: inactive. 1: active. 5: deleted.',
  `BasePrice` int(11) DEFAULT NULL,
  `PCreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `PUpdateDate` datetime NOT NULL,
  `PUrl` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  `ProviderName` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL,
  PRIMARY KEY (`ProductId`),
  UNIQUE KEY `PCode` (`PCode`),
  KEY `CategoryId` (`CategoryId`),
  KEY `BrandId` (`BrandId`),
  KEY `PName` (`PName`)
) ENGINE=MyISAM AUTO_INCREMENT=341028 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
CREATE TABLE `units` (
  `UnitId` int(11) NOT NULL AUTO_INCREMENT,
  `UnitName` varchar(25) COLLATE utf8_persian_ci NOT NULL,
  PRIMARY KEY (`UnitId`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
CREATE TABLE `products_sub` (
  `ProductSubId` int(11) NOT NULL AUTO_INCREMENT,
  `ProviderId` int(11) NOT NULL,
  `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `Price` int(11) NOT NULL,
  `ProviderPrice` int(11) NOT NULL,
  `PQuantity` int(10) NOT NULL,
  `PDeliveryTime` time NOT NULL,
  `PEditTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ProductSubId`),
  KEY `ProviderId` (`ProviderId`),
  KEY `PCode` (`PCode`)
) ENGINE=MyISAM AUTO_INCREMENT=340349 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
CREATE TABLE `provider` (
  `ProviderId` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `ProviderName` varchar(25) COLLATE utf8_persian_ci NOT NULL,
  `ProviderPhone` varchar(11) COLLATE utf8_persian_ci NOT NULL,
  `ProviderCell` varchar(11) COLLATE utf8_persian_ci NOT NULL,
  `ProviderAddress` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `ProviderDetail` text COLLATE utf8_persian_ci NOT NULL,
  `Email` varchar(50) COLLATE utf8_persian_ci NOT NULL,
  `Status` tinyint(1) NOT NULL,
  `RequestTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Permission` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  PRIMARY KEY (`ProviderId`),
  UNIQUE KEY `UserId` (`UserId`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
CREATE TABLE `order_products` (
  `OrderProductId` int(11) NOT NULL AUTO_INCREMENT,
  `OrderId` int(11) NOT NULL,
  `ProviderId` int(11) NOT NULL,
  `PCode` varchar(50) COLLATE utf8_persian_ci NOT NULL,
  `PName` varchar(50) COLLATE utf8_persian_ci NOT NULL,
  `PUnit` varchar(50) COLLATE utf8_persian_ci NOT NULL,
  `Token` int(11) NOT NULL DEFAULT '999',
  `ProductPrice` int(11) NOT NULL,
  `ProductWeight` int(11) NOT NULL,
  `ProductPurchase` int(11) NOT NULL,
  PRIMARY KEY (`OrderProductId`),
  KEY `ProductId` (`PCode`),
  KEY `OrderId` (`OrderId`),
  KEY `ProviderId` (`ProviderId`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
CREATE TABLE `brands` (
  `BrandId` int(11) NOT NULL AUTO_INCREMENT,
  `BrandName` varchar(25) NOT NULL,
  `BrandEn` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`BrandId`),
  UNIQUE KEY `BrandName` (`BrandName`)
) ENGINE=MyISAM AUTO_INCREMENT=1988 DEFAULT CHARSET=utf8

My Query:

SELECT
			`products`.`ProductId`,
			`products`.`PName`,
			`products`.`Status`,
			`products`.`PSpecial`,
			`products`.`PImage`,
			`products`.`BasePrice`,
			`products`.`CategoryId`,
			`units`.`UnitName`,
			`products_sub`.`ProductSubId`,
			`products_sub`.`ProviderId`,
			`products_sub`.`PQuantity`,
			`products_sub`.`ProviderPrice`,
			`products_sub`.`Price` AS `customerPrice`,
			`provider`.`ProviderName`,
			SUM(`order_products`.`ProductPurchase`) AS `sale`
		FROM
			`products`
		LEFT JOIN
			`brands`
		ON
		   `products`.`BrandId`=`brands`.`BrandId`
		INNER JOIN
			`units`
		ON
			`products`.`UnitId`=`units`.`UnitId`
		LEFT JOIN
			`products_sub`
		ON
			`products`.`PCode`=`products_sub`.`PCode`
		AND
			`products_sub`.`ProductSubId` = 
			(
				SELECT
					`ProductSubId`
				FROM
					`products_sub`
				WHERE
					`PCode`=`products`.`PCode`
				AND
					`PQuantity` > 0
				ORDER BY
					`ProviderPrice`
				LIMIT
					1
			)
		LEFT JOIN
			`provider`
		ON
			`products_sub`.`ProviderId`=`provider`.`ProviderId`
		LEFT JOIN
			`order_products`
		ON
			`products`.`PCode`=`order_products`.`PCode`
		WHERE
			`products`.`Status`>0
		AND
			`products`.`Status`!=5
		AND
			`products`.`CategoryId` IN (1133,1134,1137,1174,1175,1176,1138,1177,1178,1179,1139,1140,1141,1180,1181,1182,1183,1184,1142,1143,1144,1145,1185,1186,1187,1188,1189,1190,1191,1146,1147,1148,1149,1150,1192,1193,1194,1855,1856,1857,1135,1151,1152,1153,1154,1155,1156,1157,1158,1136,1159,1160,1161,1162,1163,1164,1165,1166,1195,1196,1197,1198,1199,1167,1168,1200,1201,1202,1203,1204,1205,1169,1206,1207,1208,1209,1170,1171,1172,1173,1210,1211,1858,1859,1212,1213,1214,1232,1233,1234,1215,1216,1235,1236,1237,1238,1217,1239,1240,1241,1242,1218,1219,1220,1243,1244,1245,1246,1247,1248,1221,1249,1250,1251,1222,1223,1224,1252,1253,1254,1225,1255,1256,1257,1258,1259,1260,1261,1262,1263,1226,1227,1228,1229,1230,1231,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1463,1464,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1465,1466,1467,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1468,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1469,1470,1471,1472,1473,1474,1508,1509,1286,1287,1290,1299,1395,1396,1300,1301,1397,1398,1399,1302,1303,1304,1305,1400,1401,1402,1306,1307,1308,1309,1403,1404,1310,1405,1406,1407,1408,1311,1312,1313,1314,1315,1409,1410,1291,1316,1317,1318,1319,1320,1292,1321,1322,1323,1324,1288,1293,1325,1411,1412,1326,1327,1413,1414,1328,1329,1330,1331,1415,1416,1417,1418,1332,1419,1420,1421,1422,1333,1334,1335,1423,1424,1336,1425,1426,1427,1428,1429,1430,1431,1337,1338,1432,1433,1434,1339,1340,1341,1342,1435,1436,1437,1343,1438,1439,1440,1441,1294,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1295,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1860,1861,1862,1289,1296,1364,1442,1443,1444,1365,1445,1446,1447,1448,1366,1367,1368,1369,1449,1450,1451,1452,1453,1370,1371,1372,1373,1454,1455,1456,1457,1458,1459,1374,1375,1460,1376,1377,1378,1379,1461,1462,1297,1380,1381,1382,1383,1384,1385,1386,1298,1387,1388,1389,1390,1391,1392,1393,1394,1863,1864,1865,1132)
		GROUP BY
			`products`.`PCode`
		ORDER BY
			`products`.`Status`,
			`products_sub`.`PQuantity` > 0 DESC,
			`products`.`PSpecial` DESC,
			`customerPrice` ASC,
			`products`.`BasePrice` ASC
		LIMIT
			36
		OFFSET
			0

This query is extremely slow :(

When is remove the ORDER BY section, It will run very fast.

 

As i researched, its better to use sub-queries. but didn't figure out how to use them with left join.

Any help is appreciated. thanks.

Edited by beyzad

Share this post


Link to post
Share on other sites

The sub-query will slow you down. The values in columns from products_sub and provider will be completely arbitrary as you are grouping by pcode (only one row per pcode in the output so values from those two table could come from any record with matching pcode)

As you are selecting by status and ordering by status then try it with an index on product(status)

Share this post


Link to post
Share on other sites
Posted (edited)

 

On 5/23/2019 at 9:43 PM, Barand said:

The values in columns from products_sub and provider will be completely arbitrary as you are grouping by pcode (only one row per pcode in the output so values from those two table could come from any record with matching pcode)

This is why i have in my query:

AND
			`products_sub`.`ProductSubId` = 
			(
				SELECT
					`ProductSubId`
				FROM
					`products_sub`
				WHERE
					`PCode`=`products`.`PCode`
				AND
					`PQuantity` > 0
				ORDER BY
					`ProviderPrice`
				LIMIT
					1
			)

And there is no relation between ` provider` and ` products_sub` tables using `PCode`.

 

On 5/23/2019 at 9:43 PM, Barand said:

As you are selecting by status and ordering by status then try it with an index on product(status)

Tried this. also tried to index all fields that used in ORDER BY section. same result.

The only field i can use in ORDER BY that will run fast is `PCode` itself.... :(

Edited by beyzad

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.