Jump to content

Please help to optimize this query

beyzad

By beyzad
in MySQL Help

 Share

Recommended Posts

beyzad Member

beyzad

Posted
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
Link to comment
https://forums.phpfreaks.com/topic/308746-please-help-to-optimize-this-query/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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)

beyzad Member

beyzad

Posted
  • Author
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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

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.