Einfache Postmeta Inner Join -Abfrage benötigt 2 Sekunden zur Ausführung ⇐ MySql

[Anonymous]

WordPress -Site mit rund 150000 Posts. Ich habe eine Abfrage, die mehrmals pro Seite geladen wird, und es ist unangemessen langsam - in der Reihenfolge von 2 Sek.: < /P>

Code: Select all

SELECT COUNT(*) as cnt
FROM wp_postmeta pm, wp_posts p
inner join wp_posts p2 on p2.ID = p.post_parent and p2.post_status in('order_paid', 'order_received')
WHERE p.ID = pm.post_id
AND ((p.post_status = 'publish'))
AND p.post_type = 'tc_tickets_instances'
AND pm.meta_key = 'ticket_type_id'
AND pm.meta_value IN (157404,157405,155353,155354,155355);

Ich habe das WP MySQL für Speed ​​-Plugin installiert, das neue zusammengesetzte Indizes erzeugt, und das scheint die Dinge leicht verbessert zu haben. Die Abfrage läuft von 1,5 - 2,5 Sekunden "Normalerweise" und die von uns geladene Seite, die verwendet wird, um von 13 Sekunden bis 24 Sekunden vor dem Plugin und von 8 Sekunden bis 20 Sekunden nach zu reichen. Die neuen Indizes werden erstellt. < /p>
Es scheint nichts Offensichtliches zu geben, aber vielleicht gibt es eine hinterhältige Technik, um diese Abfrage zu optimieren, die ich nicht gefunden habe? Mysql Workbench] [1]] [1] < /p>

Code: Select all

'{  \"query_block\": {.     \"select_id\": 1,
\"cost_info\": {
\"query_cost\": \"90915.83\"
},
\"nested_loop\": [
{
\"table\": {
\"table_name\": \"p\",
\"access_type\": \"ref\",
\"possible_keys\": [
\"PRIMARY\",
\"post_parent\",
\"type_status_date\"
],
\"key\": \"type_status_date\",
\"used_key_parts\": [
\"post_type\",
\"post_status\"
],
\"key_length\": \"164\",
\"ref\": [
\"const\",
\"const\"
],
\"rows_examined_per_scan\": 72012,
\"rows_produced_per_join\": 72012,
\"filtered\": \"100.00\",
\"cost_info\": {
\"read_cost\": \"10440.29\",
\"eval_cost\": \"7201.20\",
\"prefix_cost\": \"17641.49\",
\"data_read_per_join\": \"253M\"
},
\"used_columns\": [
\"ID\",
\"post_status\",
\"post_parent\",
\"post_type\"
]
}
},
{
\"table\": {
\"table_name\": \"p2\",
\"access_type\": \"eq_ref\",
\"possible_keys\": [
\"PRIMARY\"
],
\"key\": \"PRIMARY\",
\"used_key_parts\": [
\"ID\"
],
\"key_length\": \"8\",
\"ref\": [
\"motorco.p.post_parent\"
],
\"rows_examined_per_scan\": 1,
\"rows_produced_per_join\": 14402,
\"filtered\": \"20.00\",
\"cost_info\": {
\"read_cost\": \"52201.45\",
\"eval_cost\": \"1440.24\",
\"prefix_cost\": \"77044.14\",
\"data_read_per_join\": \"50M\"
},
\"used_columns\": [
\"ID\",
\"post_status\"
],
\"attached_condition\": \"(`motorco`.`p2`.`post_status` in (\'order_paid\',\'order_received\'))\"
}
},
{
\"table\": {
\"table_name\": \"pm\",
\"access_type\": \"ref\",
\"possible_keys\": [
\"PRIMARY\",
\"meta_key\",
\"meta_value\"
],
\"key\": \"PRIMARY\",
\"used_key_parts\": [
\"post_id\",
\"meta_key\"
],
\"key_length\": \"1030\",
\"ref\": [
\"motorco.p.ID\",
\"const\"
],
\"rows_examined_per_scan\": 1,
\"rows_produced_per_join\": 7580,
\"filtered\": \"50.00\",
\"cost_info\": {
\"read_cost\": \"12355.53\",
\"eval_cost\": \"758.08\",
\"prefix_cost\": \"90915.83\",
\"data_read_per_join\": \"7M\"
},
\"used_columns\": [
\"post_id\",
\"meta_key\",
\"meta_value\"
],
\"attached_condition\": \"(`motorco`.`pm`.`meta_value` in (157404,157405,155353,155354,155355))\"
}
}
]
}
}'
< /code>
Tabelle defs:  < /p>
CREATE TABLE `wp_postmeta` (
`meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
PRIMARY KEY (`post_id`,`meta_key`,`meta_id`),
UNIQUE KEY `meta_id` (`meta_id`),
KEY `meta_key` (`meta_key`,`meta_value`(32),`post_id`,`meta_id`),
KEY `meta_value` (`meta_value`(32),`meta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1487185 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `wp_posts` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_parent` bigint unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`menu_order` int NOT NULL DEFAULT '0',
`post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`comment_count` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `post_parent` (`post_parent`,`post_type`,`post_status`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`post_author`),
KEY `post_author` (`post_author`,`post_type`,`post_status`,`post_date`)
) ENGINE=InnoDB AUTO_INCREMENT=159698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

< /code>

[1]: https://i.sstatic.net/QurVFDnZ.png