WordPress › Support » Do you recognize this MySQL query?

Beer
Member
Posted 1 year 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;

burekc
Member
Posted 1 year 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!

iridiax
Member
Posted 1 year ago #

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

burekc
Member
Posted 1 year 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.

Otto42
Moderator
Posted 1 year 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

burekc
Member
Posted 1 year 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.

Otto42
Moderator
Posted 1 year 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.

WordPress.org

Forums

Do you recognize this MySQL query? (7 posts)

Topic Closed

About this Topic

Tags

Code is Poetry