homepage Welcome to WebmasterWorld Guest from 54.211.7.174
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Optimizing Mysql Query
Optimizing mysql query, explain, extended
kpowning




msg:4283043
 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...]

 

SlickSolutionsInc




msg:4288835
 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

LifeinAsia




msg:4289059
 3:53 pm on Mar 29, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved