WordPress.org

Ready to get started?Download WordPress

Forums

Do you recognize this MySQL query? (7 posts)

  1. Beer
    Member
    Posted 6 years ago #

    Do you recognize this query? Is it standard to WordPress or could be from a plugin? Thanks in advance.

    # Query_time: 438  Lock_time: 0  Rows_sent: 27  Rows_examined: 105438
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND
    wp_posts.ID NOT IN ('2052', '2053', '2054', '2055', '2056', '2133',
    '2238', '2445', '2545', '2546', '2842', '2843', '3015', '301
    6', '3069', '3103', '3104', '3105', '3121', '3136', '3157', '3160',
    '3181', '3217', '3260', '3261', '3262', '3342', '3343', '3398', '3399',
    '3463', '3489', '3490', '3516', '3566', '3567', '3612', '
    3673', '3674', '3682', '3683', '3735', '3736', '3796', '3853', '3867',
    '3887', '3899', '3973', '3974', '4014', '4068', '4069', '4218', '4320',
    '4321', '4367', '4368', '4382', '4383', '4384', '4385'
    , '4386', '4464', '4465', '4466', '4504', '4505', '4506', '4566', '4567',
    '4568', '4582', '4651', '4652', '4690', '4691', '4692', '4753', '4765',
    '4810', '4843', '4857', '4858', '4859', '4913', '49
    14', '4915', '4972', '5047', '5121', '5122', '5123', '5156', '5208',
    '5209', '5210', '5211', '5244', '5283', '5284', '5285', '5356', '5357',
    '5358', '5359', '5411', '5412', '5413', '5414', '5415',
    '5617', '5618', '5686', '5707', '5708', '5733', '5781', '5782', '5789',
    '5839', '5840', '5858', '5859', '5916', '6040', '6058', '6076', '6234',
    '6235', '6236', '6237', '6357', '6441', '6442', '6443
    ', '6444', '6450', '6451', '6452', '6490', '6569', '6600', '6601',
    '6602', '6603', '6604', '6609', '6610', '6611', '6612', '6676', '6677',
    '6678', '6741', '6850', '6851', '6852', '6946', '6970', '6
    994', '7033', '7034', '7061', '7062', '7110', '7128', '7229', '7230',
    '7312', '7313', '7384', '7433', '7434', '7435', '7436', '7465', '7466',
    '7467', '7468', '7551', '7554', '7555', '7618', '7619',
     '7639', '7640', '7641', '7642', '7643', '7670', '7671', '7698', '7740',
    '7773', '7774', '7822', '7901', '7910', '7911', '7912', '7988', '8050',
    '8051', '8052', '8102', '8103', '8130', '8150', '818
    5', '8236', '8237', '8238', '8288', '8399', '8430', '8431', '8432',
    '8467', '8468', '8517', '8518', '8563', '8617', '8618', '8636', '8637',
    '8638', '8639', '8760', '8761', '8762', '8832', '8867', '
    8868', '8869', '8915', '8916', '8917', '8918', '8944', '8950', '8951',
    '8952', '8953', '8954', '8955', '8994', '8995', '9068', '9069', '9144',
    '9189', '9190', '9191', '9234', '9235', '9236', '9292'
    , '9293', '9360', '9361', '9362', '9386', '9432', '9493', '9615', '9689',
    '9690', '9691', '9716', '9717', '9735', '9817', '9818', '9835', '9836',
    '9941', '10016', '10017', '10086', '10087', '10099'
    , '10120', '10121', '10130', '10232', '10288', '10350', '10351', '10441',
    '10557', '10558', '10720', '10754', '10850', '10851', '10979', '10980',
    '11007', '11067', '11101', '11102', '11126', '11127
    ', '11128', '11207', '11208', '11209', '11210', '11211', '11212',
    '11249', '11448', '11482', '11609', '11643', '11644', '11645', '11646',
    '11720', '11721', '11722', '11723', '11724', '11725', '1211
    4', '12115', '12198', '12359', '12360', '12361', '12362', '12569',
    '12804', '13002', '13003', '13004', '13142', '13143', '13226', '13471',
    '13472', '13633', '13634', '13746', '13747', '13748', '137
    49', '13750', '13871', '13872', '13873', '13874', '13875', '14003',
    '14207', '14208', '14306', '14307', '14308', '14505', '14648', '14649',
    '14650', '14651', '14652', '14653', '14654', '14655', '14
    656', '14657', '14658', '14659', '14801', '14926', '14953', '14954',
    '14987', '14988', '15273', '15274', '15405', '15487', '16000', '16001',
    '16002', '16286', '16453', '16454', '16564', '16649', '1
    6768', '16769', '16917', '16918', '17169', '17170', '17171', '17172',
    '17334', '17362', '17363', '17587', '17588', '17770', '17818', '18109',
    '18215', '18260', '18261', '18425', '18426', '18427', '
    18513', '18611', '18636', '18637', '19071', '19194', '19217', '19714',
    '19715', '19716', '19923', '19924', '20121', '20284', '20419', '20555',
    '20556', '20683', '20713', '20714', '20788', '20789',
    '21320', '21455', '21751', '21752', '21925', '22015', '22587', '22588',
    '22619', '22630', '22758', '23079', '23177', '23418', '23419', '23420',
    '23421', '23422', '23423', '23424', '23425', '23426',
     '23427', '23428', '23429', '23430', '23431', '23432', '23433', '23434',
    '23435', '23436', '23437', '23438', '23439', '23440', '23441', '23442',
    '23443', '23444', '23445', '23446', '23447', '23448'
    , '23449', '23450', '23451', '23452', '23453', '23454', '23455', '23456',
    '23457', '23458', '23459', '23460', '23461', '23462', '23463', '23464',
    '23465', '23466', '23467', '23539', '23665', '23666
    ', '23667', '23668', '23669', '23866', '23926', '24140', '24141',
    '24142', '24156', '24157', '24158', '24175', '24217', '24218', '24532',
    '24533', '24534', '24535', '24536', '24537', '24538', '2453
    9', '24540', '24541', '24542', '24543', '24544', '24545', '24546',
    '24547', '24548', '24549', '24550', '24551', '24552', '24553', '24554',
    '24555', '24556', '24557', '24558', '24559', '24560', '245
    61', '24562', '24563', '24564', '24565', '24566', '24567', '24568',
    '24569', '24570', '24571', '24572', '24573', '24574', '24575', '24576',
    '24577', '24578', '24579', '24580', '24581', '24618', '24
    619', '24620', '24621', '24788', '24789', '24918', '24986', '25000',
    '25001', '25046', '25047', '25048', '25049', '25176', '25266', '25267',
    '25282', '25313', '25314', '25360', '25361', '25362', '2
    5385', '25386', '25387', '25388', '25426', '25468', '25469', '25495',
    '25496', '25497', '25498', '25499', '25584', '25597', '25598', '25657',
    '25690', '25691', '25692', '25730', '25763', '25928', '
    25950', '25951', '25952', '25953', '25987', '26083', '26084', '26085',
    '26118', '26171', '26172', '26173', '26174', '26175', '26176', '26203',
    '26228', '26229', '26267', '26268', '26305', '26332',
    '26333', '26334', '26387', '26388', '26421', '26451', '26452', '26480',
    '26515', '26541', '26582', '26598', '26665', '26666', '26688', '26715',
    '26771', '26939', '27170', '27237', '27637', '27638',
     '27639', '27640', '27677', '27678', '27679', '27751', '27752', '27753',
    '27964', '28053', '28133', '28134', '28240', '28289', '28290', '28419',
    '28504', '28633', '28666', '28667', '28724', '28983'
    , '29012', '29043', '29044', '29063', '29092', '29093', '29094', '29130',
    '29198', '29257', '29258', '29270', '29328', '29329', '29357', '29358',
    '29504', '29639', '29671', '29731', '29732', '29733
    ', '29778', '29831', '29873', '29874', '29875', '29876', '29925',
    '29926', '29927', '29928', '29952', '29953', '30004', '30005', '30137',
    '30263', '30264', '30377', '30378', '30517', '30518', '3060
    2', '30627', '30685', '30971', '31073', '31074', '31075', '31076',
    '31077', '31078', '31079', '31128', '31129', '31147', '31148', '31149',
    '31150', '31151', '31152', '31153', '31154', '31155', '311
    56', '31157', '31158', '31231', '31296', '31333', '31334', '31530',
    '31635', '31775', '39811', '39873', '39874', '39875', '39876', '39877',
    '39878', '39879', '39880', '39881', '39882', '39883', '39
    884', '39885', '39886', '39887', '39888', '39889', '39890', '39891',
    '39892', '39893', '39894', '39895', '39896', '39897', '39898', '39899',
    '39900', '39901', '39902', '39903', '39904', '39905', '3
    9906', '39907', '39908', '39909', '39910', '39911', '39912', '39913',
    '39914', '39915', '39916', '39917', '39918', '39919', '39920', '39921',
    '39922', '44979', '44980', '44981', '44982', '44983', '
    44984', '44985', '44986', '44987', '44988', '44989', '44990', '44991',
    '44992', '44993', '44994', '44995', '44996', '44997', '44998', '44999',
    '45000', '45001', '45002', '45003', '45004', '45005',
    '45006', '45007', '45008', '45009', '45010', '45011', '45012', '45013',
    '45014', '45015', '45016', '45017', '45018', '45019', '45020', '45021',
    '45022', '45023', '45024', '45025', '45026', '45027',
     '45028', '45642', '45643', '45644', '45645', '45646', '45647', '45648',
    '45649', '45650', '45651', '45653', '45655', '45657', '45658', '45659',
    '45660', '45662', '45663', '45665', '45667', '45668'
    , '45669', '45671', '45672', '45674', '45675', '45677', '45678', '45680',
    '45681', '45682', '45683', '45684', '45686', '45687', '45688', '45689',
    '45690', '45691', '45692', '47457', '47458', '47459
    ', '47460', '47461', '47462', '47463', '47464', '47465', '47466',
    '47467', '47468', '47469', '47470', '47471', '47472', '47473', '47474',
    '47475', '47476', '47477', '47478', '47479', '47480', '4748
    1', '47482', '47483', '47484', '47485', '47486', '47487', '47488',
    '47489', '47490', '47491', '47492', '47493', '47494', '47495', '47496',
    '47497', '47498', '47499', '47500', '47501', '47502', '475
    03', '47504', '47505', '47506', '49401', '49402', '49403', '49404',
    '49405', '49406', '49407', '49408', '49409', '49410', '49411', '49412',
    '49413', '49414', '49415', '49416', '49417', '49418', '49
    419', '49420', '49421', '49422', '49423', '49424', '49425', '49426',
    '49427', '49428', '49429', '49430', '49431', '51247', '51248', '51249',
    '51250', '51251', '51252', '51253', '51254', '52752', '5
    2753', '52754', '52755', '52756', '52757', '52758', '52759', '52760',
    '52761', '52762', '52763', '52764', '52765') AND wp_posts.post_type =
    'post' AND (wp_posts.post_status = 'publish') GROUP BY wp
    _posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 50;
  2. burekc
    Member
    Posted 5 years ago #

    Hi,
    Did you ever get an answer to this question? I have the same query on my site and it's extremely slow. I've disabled all plugins and still see the query. Thanks!

  3. iridiax
    Member
    Posted 5 years ago #

    Try temporarily switching to the default theme and see if anything improves.

  4. burekc
    Member
    Posted 5 years ago #

    Thanks so much for your response. I've tried switching to the default theme and no luck. The query is actually really slow from the mysql command line. We have 200000+ posts and 50 categories. This is the query that gets run from the top level category (which is parent to all 50 categories). The site only displays 20 entries but MySQL is creating a temporary table to scan the results because of the group by clause. The order by seems to cause MySQL to use a filesort as well. There are indexes on the tables, but it doesn't look like they're being used efficiently. I'm not sure where to go with this other than to try to rewrite the query more efficiently.

  5. Samuel Wood (Otto)
    Tech Ninja
    Posted 5 years ago #

    The given SQL comes from a query_posts statement which is set to:
    1. Pull 50 posts.
    2. Exclude the posts from a specific category or set of categories.

    The post status, type, date, and ID are all indexed as a single key, so a temporary table should not be necessary for that query.

    However, I can see where the group by would need a temp table to do the work.

    There is a line of code in wp-includes\query.php that adds that group by, when I'm not entirely certain that it is necessary... Here's the relevant code:

    if ( !empty($q['category__in']) || !empty($q['category__not_in']) || !empty($q['category__and']) ) {
    	$groupby = "{$wpdb->posts}.ID";
    }

    Try removing that bit. If you get duplicate posts in the results, then it is necessary after all.

    Brought up the issue in trac:
    http://trac.wordpress.org/ticket/7761

  6. burekc
    Member
    Posted 5 years ago #

    I think I spoke a little too soon. It seems that removing the Group By does speed up the query, however if I have it choose from just one category, it's still using the temporary table.

  7. Samuel Wood (Otto)
    Tech Ninja
    Posted 5 years ago #

    Querying from a single category uses an INNER JOIN as well as a GROUP BY. However, in that case, the group by might actually be necessary, due to the join.

Topic Closed

This topic has been closed to new replies.

About this Topic