beyzad

Any advice?

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`. 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....

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.

Hello sir. My indexes are shown in the SQL provided above. There are 53.7MB data + 7MB index @ factors table and 88.9MB data + 13.1MB index @ factor_times table. If you have any suggestion changing my indexes, I'll appreciate that. This is not working. shouldn't the conditions for RIGHT table be in join part of query?

Hello there. I have 2 table: factors: CREATE TABLE IF NOT EXISTS `factors` ( `vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL, `factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL, `factor_status` enum('0','1','2','3','4','5','6','7','8','9','10','11') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0: pending. 1: ready. 2:sent. 3:delivered. 4:canceled. 5:returned. 6:return_confirmed. 7: canceled_by_post. 8: banned. 9: waiting. 10: failed CASH. 11: transferred', UNIQUE KEY `factor_key` (`factor_key`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; factor_times: CREATE TABLE IF NOT EXISTS `factor_times` ( `factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL, `factor_status` int(11) NOT NULL, `factor_time` int(11) NOT NULL, KEY `factor_key` (`factor_key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Factor times will save timestamp for each status (0 to 11). each factor_key can have unlimited statuses and times. for example, a factor can go to status 1, then go back to 0, then go to 2, then 3, then back to 1 .... What i want, is to export first time of statuss of (0,2,3,6) for each day of the month. for example, i want to export orders that been in status (0,2,3,6) in November, grouped day by day. What i tried is something like this: SELECT `factor_times`.`factor_status`, MIN(`factor_times`.`factor_time`) FROM `factor_times` INNER JOIN `factors` ON `factor_times`.`factor_key`=`factors`.`factor_key` AND `factors`.`vendor_id`='03010001' WHERE `factor_times`.`factor_status` IN (0,2,3,6) AND `factor_times`.`factor_time` > 1437507000 AND `factor_times`.`factor_time` > 1440185400 GROUP BY `factor_times`.`factor_key` This used to export all times in a period, so i can use PHP code to assign them to each day of month. But it takes so so so so so so long to execute. factor rows: 246,718 factor_times rows: 2,915,127 Sorry for poor English :happy-04:

Thanks. I owe you :happy-04:

Hi there. I have a table named `cat` that looks like this: id |name |parent --------+---------------+------ 1 |cat1 |0 --------+---------------+------ 2 |cat2 |0 --------+---------------+------ 3 |subcat1 |1 --------+---------------+------ 4 |subcat2 |1 --------+---------------+------ 5 |subsubcat1 |3 --------+---------------+------ I need to export something like this with only 1 query if it is possible: id |name |parent(name) --------+---------------+------------ 1 |cat1 |NULL --------+---------------+------------ 2 |cat2 |NULL --------+---------------+------------ 3 |subcat1 |cat1 --------+---------------+------------ 4 |subcat2 |cat1 --------+---------------+------------ 5 |subsubcat1 |subcat1 --------+---------------+------------ Thanks.

Hi there. I have 2 tables like this: factor_times: +------------+---------------------+--------+ | factor_key | factor_time | status | +------------+---------------------+--------+ | 1 | 1313131313 | 1 | | 1 | 1313131314 | 2 | | 1 | 1313131315 | 1 | | 1 | 1313131316 | 2 | | 2 | 1313131317 | 1 | | 2 | 1313131318 | 3 | | 3 | 1313131319 | 1 | | 3 | 1313131320 | 6 | +------------+---------------------+--------+ and factors: +------------+---------------+ | factor_key | factor_status | +------------+---------------+ | 1 | 2 | | 2 | 3 | | 3 | 6 | +------------+---------------+ now, I want to make a query that will results all factors, Maximum time of current status and its status with specified time range. I used a query like this: SELECT `factors`.*,`factor_times`.`factor_time` AS `update` FROM `factors` LEFT JOIN `factor_times` ON `factors`.`factor_key`=`factor_times`.`factor_key` AND `factor_times`.`factor_status`=2 AND `factor_times`.`factor_time` <= 1313131320 AND `factor_times`.`factor_time` >= 1313131313 WHERE `factors`.`factor_status`='2' I also must say that a factor can have multiple times in a single status. For example, factor status can be changed to 2, then 1, then again 2, then 3, then again 2 and go on. Thanks

Ok looks like i am doing wrong or something. Here is what i wanna do: - I am creating a game using HTML. - For a section of game, I have to put 4 images on top of each other. For example: face, eyes, nose and mouth. - I need to make 2 buttons for each image so people can swith to next, previous images in array. - It's better to avoid using AJAX. so i want to do this only with javascript and CSS. Thanks.

Hi. I need the exact file name. I have no problem using php as well. Thanks.

Hi there. I have a problem getting an element background image. If i use class and define it in external css, i can't even get the background. If i use style argument, the output is vary in browsers. For example in IE & FF the output is: url:("image.png") But in chrome the output is: url:("file:///c:/users/.....") Any suggestion? Thanks

Thank you sir. Works perfect.

Thanks you sir. Here is the conf i tried to do. but my problem is all A records are pointer to a same ip. Additional info is the OS is windows 7 <VirtualHost 127.0.0.2> DocumentRoot "H:/alavian/dl" ServerName dl.yasdownload.com </VirtualHost> <VirtualHost 127.0.0.3> DocumentRoot "H:/alavian/dl1" ServerName dl1.yasdownload.com </VirtualHost> <VirtualHost 127.0.0.4> DocumentRoot "H:/alavian/dl2" ServerName dl2.yasdownload.com </VirtualHost> <VirtualHost 127.0.0.5> DocumentRoot "H:/alavian/dl3" ServerName dl3.yasdownload.com </VirtualHost> <VirtualHost 127.0.0.6> DocumentRoot "H:/alavian/dl4" ServerName dl4.yasdownload.com </VirtualHost> <VirtualHost 127.0.0.7> DocumentRoot "H:/alavian/dl5" ServerName dl5.yasdownload.com </VirtualHost> I also tried to write to host file. but IDK how to convert requested server name to local IP. Thanks.

The server is right behind me

Hi there. I have 6 A records on a domain that points to my server. And my server has only 1 IP. There is no control panel installed and it is a poor apache web server. So how can i assign a folder for each A record? Thanks.