Welcome to WebmasterWorld Guest from 23.21.38.201

Forum Moderators: open

Optimizing Mysql Query

Optimizing mysql query, explain, extended

   
3:38 pm on Mar 17, 2011 (gmt 0)



I have the following mysql query and bottlenecking shows this query is slow. I need to optimize this but I have no Idea whay MYSQL EXPLAIN as well as EXTENDED is telling me about the query and help would be greatly appreciated.


SQL: SELECT DISTINCT p.productid FROM xcart_products AS p LEFT JOIN xcart_products_categories AS pc ON pc.productid = p.productid LEFT JOIN xcart_categories AS c ON pc.categoryid = c.categoryid LEFT JOIN xcart_images_T AS i ON i.id = p.productid LEFT JOIN xcart_extra_field_values AS e ON e.productid = p.productid AND e.fieldid = '1' WHERE p.forsale = 'Y' AND c.avail = 'Y' AND c.categoryid IN (134, 134, 135, 136, 137, 138, 139, 140, 141, 143, 144, 145, 147, 148, 149, 150, 151, 152, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 180, 182, 184, 185, 186, 187, 188, 190, 191, 192, 193, 194, 196, 197, 198, 200, 201, 202, 203, 204, 205, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 219, 221, 222, 223, 224, 225, 226, 233, 234, 235, 237, 238, 239, 240, 242, 243, 245, 246, 248, 249, 250, 251, 252, 253, 254, 255, 256, 259, 260, 261, 262, 263, 266, 268, 270, 271, 273, 274, 276, 277, 280, 281, 283, 284, 285, 286, 290, 291, 292, 296, 301, 304, 305, 306, 307, 309, 310, 312, 313, 315, 317, 318, 319, 321, 322, 326, 328, 330, 331, 332, 333, 335, 337, 495, 497, 498, 499, 502, 516, 517, 545, 546, 547, 564, 565, 568, 569, 570, 571, 572, 573, 586, 587, 588, 589, 592, 601, 602, 603, 604, 605, 606, 607, 609, 610, 611, 613, 614, 619, 620, 625, 626, 627, 628, 631, 643, 644, 645, 646, 647, 653, 654, 655, 661, 663, 664, 669, 674, 675, 676, 677, 678, 679, 680, 682, 684, 688, 689, 690, 691, 692, 693, 694, 697, 700, 701, 706, 707, 709, 711, 713, 714, 715, 716, 717, 718, 719, 720, 723, 724, 726, 728, 729, 730, 731, 732, 739, 740, 741, 742, 743, 744, 745, 746, 747, 748, 749, 750, 751, 752, 753, 754, 755, 756, 757, 759, 761, 762, 763, 764, 765, 771, 774, 775, 776, 777, 778, 781, 782, 783, 785, 786, 787, 790, 793, 800, 802, 803, 804, 805, 806, 807, 808, 809, 810, 811, 812, 813, 814, 826, 827, 828, 829, 831, 834, 840, 847, 850, 862) AND e.value >= '1297366729' GROUP BY i.id ORDER BY RAND() LIMIT 4


View MYSQL EXPLAIN using the link below: (Didnt know how to post images on this forum)
[theerotictoystore.com...]
4:06 am on Mar 29, 2011 (gmt 0)



I am not familiar with the statistic but I took a swag at what the indexes should be.

The joins should be on indexed primary keys and indexed foreign keys.

e.productid AND e.fieldid = Index
e.fieldid = Index - May not need this but see what happens and delete if never used.
c.avail AND c.categoryid = Index
i.id = Index
p.forsale = Index

pc.productid = p.productid = PK and FK index
pc.categoryid = c.categoryid = PK and FK index
i.id = p.productid = PK and FK index
3:53 pm on Mar 29, 2011 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Is e.value actually a VARCHAR field? If so, you may run into some problems with the e.value >= '1297366729' part. VARCHAR numeric values don't always behave like true numeric values for sorting/comparison.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month