Optimizing Mysql Query

Optimizing mysql query, explain, extended

[kpowning]

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

[SlickSolutionsInc]

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

[LifeinAsia]

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.