Slow query on all page (1500 category)

  • jrsmrtn

    (@jrsmrtn)


    5 years ago

    Hello, I have a slow query about 30sec on all page, I think its about the 1500 category of the site. But i d’ont find more information for fix this slow query. the server is a 4 core 8gb ram. Tell me want you need for help me…

    The query :

    SELECT object_id, term_taxonomy_id
FROM qhUj1_term_relationships
INNER JOIN qhUj1_posts
ON object_id = ID
WHERE term_taxonomy_id IN (15,12597,3361,20511,8377,10396,9350,10012,1342,20726,20291,7728,20512,2196,2252,8376,8379,11180,20527,10397,22318,10363,10664,22,23,6690,12852,22691,7246,3322,7266,20288,9351,20449,12764,1341,19,35389,49175,42678,43864,448449,333154,205091,64928,172795,365092,289402,38954,263534,32034,34768,35016,35409,36981,398529,35408,36416,32033,56059,149850,49944,47757,145921,735921,660298,209249,40503,40506,740428,729541,59696,40509,41092,40556,359148,276736,335509,56660,35399,44054,35401,34685,43920,43840,37270,61351,190236,154615,43839,71067,32014,44030,43961,42396,44076,43848,60659,32013,44112,53697,487727,51347,53174,331668,32433,43851,35056,37569,35001,44005,34713,41604,25,24,9246,7726,6467,9524,30178,3669,29882,1420,1419,12665,2137,24237,24135,1646,1482,43813,149550,43812,51288,43811,57147,64250,64391,235734,64253,64259,69214,61740,64261,55325,57106,205638,557234,374341,378934,333705,382490,58273,212122,359169,235416,367066,61172,99171,224141,378552,331374,338222,338194,280469,337127,205505,205615,205551,20110,1647,2138,9051,20,1586,20289,6891,1340,8956,21097,20266,2636,5392,551,550,44326,594578,58586,58357,230203,35627,498030,455456,420996,35626,35628,331513,68183,40012,40011,12523,1584,10709,10786,18442,19198,3363,7062,2589,18441,20379,6166,24239,21098,2418,1343,24112,22623,9769,20575,2832,10495,9551,12599,12600,6414,11664,10134,930,12628,12629,22441,22446,13968,12347,17,9687,10380,9752,9751,9686,20267,20592,9600,12876,975,22629,9689,18362,9690,8819,8818,10250,3658,18,29267,6260,3412,8768,259317,193799,259052,259123,259176,203542,279676,257944,258846,202705,258041,203207,68127,197287,107470,203362,68126,259117,76336,195574,130556,579995,92643,68895,198613,68894,258749,183817,76337,257994,204464,197374,76089,76080,193759,130554,764287,259309,193749,203085,31181,11250,29746,31180,11337,20292,11663,11249,3275,10188,2699,4169,3413,6470,6468,47475,232394,70692,47474,150977,151039,33923,34884,210423,201872,154194,38188,55847,38992,37824,89132,155999,6469,973,35238,57996,58198,35118,45932,150379,92022,89159,338398,88613,50478,1773,20760,20806,6942,29496,7456,8931,8896,873,2446,9324,9900,2481,1645,20790,2165,3950,13783,2635,9416,7452,20514,57087,50065,197355,50064,329790,146072,204029,204028,762160,205199,52397,203996,89407,204306,205123,203924,53327,205275,205274,722122,8932,20503,7453,20574,8929,20508,8533,19916,6415,9581,1705,6321,6890,24089,6320,1587,20287,933,6171,3278,9995,35490,57001,31957,31969,34750,73440,75297,76551,88171,35486,34749,50544,70371,561030,36452,68088,34752,37753,51279,35595,68100,146609,9327,3320,2445,12790,7156,20620,15121,11247,7097,8412,7321,29185,7098,20619,1815,20496,12627,20507,20499,878,6170,1418,49052,220782,371345,217860,284297,529766,461291,217859,190592,191496,460886,53929,288512,183530,189471,217901,190811,183242,183241,183837,461274,217908,67169,218702,217909,218891,190550,218877,460887,220727,461409,151681,192661,192660,460836,218809,218738,389777,460875,279955,67159,217844,217843,218895,217847,218769,22767,7129,6130,12736,297352,398832,297351,398493,494191,398275,757870,354331,872,1339,43236,44785,44813,522246,43235,44946,35404,44875,35403,69205,61696,44808,44816,44890,44895,44903,44851,35458,35457,39589,43944,40000,44353,44352,52306,44912,44351,56961,52277,55714,52272,55613,35170,44834,57769,44805,51311,35169,44839,44838,44870,44879,35048,39581,39600,52103,41735,35047,24061,8350,3172,6338,9470,13629,9349,7455,7454,12716,8897,20290,875,40524,146028,61603,45714,70043,137859,149828,40523,148351,41375,145898,85856,53113,67826,45750,68391,35099,50343,38194,155287,36755,54682,53416,148331,149998,147579,149871,54200,352951,58166,148329,35513,45717,52276,45974,32271,148895,145666,148460,57015,57115,44069,35072,35235,218164,100024,200562,321237,35291,35107,34674,45972,35046,32356,131315,35173,52249,247646,320914,35069,344239,372809,57390,335164,367967,58046,35162,247638,35110,261565,35164,259567,69181,52310,58266,147073,35114,32355,245655,58288,245770,352343,320513,361250,131300,358100,52468,147638,35121,35120,61272,58270,35181,344196,245696,58215,344203,61734,55438,61741,342262,52254,69185,231436,321319,248302,52253,149870,249401,369384,357850,229874,335081,530757,348275,335078,229167,368555,368557,203497,598000,530837,386794,595745,249395,355258,669942,196147,154626,213540,366655,765000,634053,343610,448539,493994,249434,377191,249462,149869,321638,321634,321543,370122,321397,228941,230410,372204,354793,228871,372217,229042,583176,635427,657450,368338,288928,494128,523825,231149,660858,371636,321547,231055,377189,546174,230965,321004,581146,357686,352942,382948,230884,367367,342505,231155,563679,563662,551946,320816,231439,377989,530707,247581,229342,624710,367594,362887,354946,362922,414300,636474,35154,58175,245639,379826,348931,561173,561194,373249,383591,247973,246441,417690,523384,247374,35153,7029,877,874,4028,4171,9352,10469,2873,3025,3023,20286,1546,194387,190645,76069,189450,121453,35060,130590,195720,258458,54001,147307,53853,194312,55080,363981,189529,188893,189697,6167,2831,2700,3024,7451,3449,5417,8194,2525,232701,35290,245760,69940,233747,218541,194572,68214,191441,197156,232581,56669,8193,9599,7725,8933,2638,1772,14262,2523,20536,12746,31464,3277,20529,7404,12596,7228,20520,150,14352,20280,20767,4202,12737,6781,8192,12346,13112,2419,24085,3447,2637,3276,20588,11181,6246,7625,972,99101,182926,183032,182928,56927,131149,56926,130629,130324,61699,153264,153609,61698,131353,69227,386565,380465,33412,32113,76885,153074,183062,131176,40594,152243,121119,336202,336192,336174,336157,182963,52330,52432,69618,130292,121480,69232,152940,151621,76181,85454,130644,131153,8930,9688,1483,19231,7457,8378,12575,2003,20615,15135,20639,20546,3321,20502,1484,1485,876,12368,932,13967,931,12832,4170,2471,6247,2042,2253,20480,24523,16,35668,53468,150636,70033,85226,35265,69134,35264,53254,358214,43979,44071,251836,43978,44011,60626,53178,86585,360406,183528,218795,220811,220787,334463,220814,53177,203806,86595,67612,35559,43914,43921,338519,287690,43884,43823,151641,63071,43959,245672,49042,137853,43847,217886,218752,43846,187915,44027,251659,68516,55959,92450,84778,34870,45945,152817,63241,150750,169576,53115,53134,187790,53304,187785,183550,35074,45943,45940,148147,50481,69129,35196,342129,44050,229169,560028,333596,560032,509989,333592,560029,149831,229973,287963,676008,287888,45934,230044,63107,523470,56769,287693,225386,56768,85394,251934,229277,229276,482464,344882,365339,230560,425424,368781,131263,230545,150683,252415,642629,131262,230338,146771,332357,624904,45938,45937,230183,290405,44049,44048,230942,563397,169570,230630,492554,51154,668040,169563,51153,169356,399672,531121,411425,34787,40737,61318,34786,44132,61563,41309,44133,37589,43858,44109,44006,43901,38175,69370,40650,51292,38174,184958,51526,96953,183237,35036,376239,49330,98797,70174,147560,86026,147670,3970,2698,9284,20581,934,9714,3951,10596,10530,10597,3448,10133,5393,22151,24360,3237,6741,20587,6742,18454,6215,7302,8436,20441,20442,1338,40366,40365,163048,165438,51516,209513,62535,208396,208413,290063,149824,346167,208487,154631,208449,625465,625466,208608,44397,220942,52381,609735,58347,149983,70027,208612,156874,764023,764022,44918,380466,547118,68081,762727,360828,209044,145798,50301,203933,50340,53204,765493,547111,720146,68095,547096,547095,147122,7526,9437,7527,9415,1751,2166,4203,1648,12594,10197,1644,1816,1928,9283,6689,12598,1817,1585,20998,20737,974,12595,20492,8191,9464,11260,20491,7727,1480,50572,193147,364486,172445,193944,346238,130548,364520,204555,284096,513818,203522,193932,52228,360326,661096,196780,414313,53336,279639,192851,121471,410717,291082,121470,68084,509971,68079,320546,149918,68105,756971,54014,197632,54013,244371,279578,755856,3238,2482,195229,53525,166201,380468,49182,165404,284225,212278,249062,284304,318976,197136,146863,189662,66820,283898,220626,90422,212276,200547,71007,55688,53407,53151,192633,70595,221517,502511,70416,467248,21,53583,352646,189152,290364,369634,276475,279703,192766,289348,53582,269399,147045,358827,358826,386518,154599,335086,320523,156730,154598,522284,330403,58018,58017,564747,320797,33170,152267,71610,203549,71609,183187,344419,49869,196061,49868,279652,195433,33169,384055,245775,489982,357280,342063,202497,282696,382911,282698,363797,70035,53297,279661,328815,205416,289223,217882,45922,194706,195489,84132,202357,82676,61268,195616,585140,114282,68111,85296,247675,88462,55078,35179,514047,194770,228761,202863,222908,196167,342121,45983,127760,194811,342081,35093,164487,54104,60563,35234,329377,270427,52427,205377,195097,195096,336204,35294,54885,35092,68114,68113,284086,284042,330643,198240,282689,328132,68124,1421,35187,52193,195224,247312,35186,148578,35184,270103,35183,258221,336200,347011,257989,35089,164457,60559,35101,177202,45916,45915,60556,2043,2285,69297,69296,75931,80588,80587,121403,121425,183060,212281,33279,40642,61115,20618,6168,9312,9550,1703,2251,9191,6169,3362,9304,11248,20547,20557,20500,20748,12449,7372,22359,12735,29758,9525,3171,9768,8898,21902,3765,3531,24060,1481,70053,43867,53312,184423,68089,67164,67779,67167,85200,52628,9438,2524,6943,8817,7061,29158,9469,1702,1704,4506,20617,24238,20515,20524,10187,8397,9918,8957,19902,12601,12626)
AND post_type IN ('product')
AND post_status = 'publish'

    The page I need help with: [log in to see the link]

Viewing 8 replies - 1 through 8 (of 8 total)
  • JNashHawkins

    (@jnashhawkins)

    That seems more like a SQL server performance issue to me and not a WordPress problem.

    Of course, if you have 1500 categories on the site, then I’d imagine it would take some time but WordPress didn’t create those categories by itself. That was a choice someone (or everyone) on the site decided on.

    There’s always a practical limit to what your hardware can deal with. You’ll either need to make some decisions to remove some of the load or else buy more horsepower.

    Thread Starter jrsmrtn

    (@jrsmrtn)

    Yes i need these category but i dont understand why it load all of that on a smple home page where no category navigation and no product and nothing… Its same problem when i load a product page, WordPress take 35sec, on the product page its just a product with no category navigation…

    That’s a considerably large number of products more of an SQL / optimization issue instead of a WordPress / WooCommerce issue.

    As was mentioned there is a practical limit on what your hardware can deal with but your site has 300,000+ products. This would require substantial optimization and tweaking to get running smoothly as that’s an enterprise size site.

    Thread Starter jrsmrtn

    (@jrsmrtn)

    The query create 40 000 000 rows in answer i think is not a mysql optimization what this need but a modification of wordpress for create a more appropriated sql request… and dont do these request on every page that not need it. No ?

    Thread Starter jrsmrtn

    (@jrsmrtn)

    Just for information on an other server with some commercial site i have optimized the server for the 80 pagespeed score, in fact the server is optimized. Other information the same database (product and category) is used with another application on a 8 core 8gb ram and a request for show a product take 0.1sec

    Thread Starter jrsmrtn

    (@jrsmrtn)

    And bigger server cant help me i think, because it is one request taking long time and one mysql request not use more than one core

    This isn’t a WordPress issue you would want to install a plugin such as Query Monitor to debug what is calling this query. It could be your theme or it could be WooCommerce.

    Once there you should contact their support teams.

    I would also suggest setting up an object cache such as Memcached or Redis to save the results of the query.

    I would assume it has to do with this large block located on the frontend of the site starting with the following on line 742 on the homepage.

    var wc_product_block_data = JSON.parse( decodeURIComponent(

    I would reach out to WooCommerce support.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Slow query on all page (1500 category)’ is closed to new replies.