WordPress.org

Support

Support » How-To and Troubleshooting » Do you recognize this MySQL query?

Do you recognize this MySQL query?

  • 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;
Viewing 6 replies - 1 through 6 (of 6 total)
  • burekc
    Member

    @burekc

    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!

    iridiax
    Member

    @iridiax

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

    burekc
    Member

    @burekc

    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.

    Samuel Wood (Otto)
    Keymaster

    @otto42

    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

    burekc
    Member

    @burekc

    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.

    Samuel Wood (Otto)
    Keymaster

    @otto42

    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.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Do you recognize this MySQL query?’ is closed to new replies.