Serious error with MySQL query

  • Resolved rpamfil

    (@rpamfil)


    6 years, 1 month ago

    Hi,
    I find that my WordPress site keep hanging when I go to the “all posts” page, on a very SQL query involving tribe_events (which takes 40sec+).
    The issue with the query is that it incorrectly uses OR conditions when joining tables, when it should be using AND. See below.
    Can you confirm that this is coming from your plugin, and fix the bug?
    Presumably this was introduced with the last version of your plugin 4.6.12 (the issue appeared recently).
    Thanks!

    mysql> SELECT wp_8ys2vk_posts.ID, count(*) /* added count to show why query is slow */
    -> FROM wp_8ys2vk_posts
    -> INNER JOIN wp_8ys2vk_postmeta ON ( wp_8ys2vk_posts.ID = wp_8ys2vk_postmeta.post_id )
    -> INNER JOIN wp_8ys2vk_postmeta AS mt1 ON ( wp_8ys2vk_posts.ID = mt1.post_id )
    -> INNER JOIN wp_8ys2vk_postmeta AS mt2 ON ( wp_8ys2vk_posts.ID = mt2.post_id )
    -> WHERE (
    -> ( wp_8ys2vk_postmeta.meta_key = ‘_VenueAddress’ AND wp_8ys2vk_postmeta.meta_value != ” )
    -> OR ( mt1.meta_key = ‘_VenueCity’ AND mt1.meta_value != ” )
    -> OR ( mt2.meta_key = ‘_VenueZip’ AND mt2.meta_value != ” )
    -> )
    -> AND wp_8ys2vk_posts.post_type = ‘tribe_venue’
    -> AND ((wp_8ys2vk_posts.post_status <> ‘trash’ AND wp_8ys2vk_posts.post_status <> ‘auto-draft’))
    -> GROUP BY wp_8ys2vk_posts.ID ORDER BY wp_8ys2vk_posts.ID ASC;

    +——-+———-+
    | ID | count(*) |
    +——-+———-+
    | 13355 | 6487 |
    | 13359 | 6769 |
    …
    | 28354 | 10981 |
    | 28356 | 10621 |
    +——-+———-+
    59 rows in set (18.71 sec)

    mysql>

    —
    REVISED QUERY with AND instead of OR for mt1 and mt2 conditions

    mysql> SELECT wp_8ys2vk_posts.ID, count(*)
    -> FROM wp_8ys2vk_posts
    -> INNER JOIN wp_8ys2vk_postmeta ON ( wp_8ys2vk_posts.ID = wp_8ys2vk_postmeta.post_id )
    -> INNER JOIN wp_8ys2vk_postmeta AS mt1 ON ( wp_8ys2vk_posts.ID = mt1.post_id )
    -> INNER JOIN wp_8ys2vk_postmeta AS mt2 ON ( wp_8ys2vk_posts.ID = mt2.post_id )
    -> WHERE (
    -> ( wp_8ys2vk_postmeta.meta_key = ‘_VenueAddress’ AND wp_8ys2vk_postmeta.meta_value != ” )
    -> AND ( mt1.meta_key = ‘_VenueCity’ AND mt1.meta_value != ” )
    -> AND ( mt2.meta_key = ‘_VenueZip’ AND mt2.meta_value != ” )
    -> )
    -> AND wp_8ys2vk_posts.post_type = ‘tribe_venue’
    -> AND ((wp_8ys2vk_posts.post_status <> ‘trash’ AND wp_8ys2vk_posts.post_status <> ‘auto-draft’))
    -> GROUP BY wp_8ys2vk_posts.ID ORDER BY wp_8ys2vk_posts.ID ASC;
    +——-+———-+
    | ID | count(*) |
    +——-+———-+
    | 13355 | 1 |
    | 13359 | 1 |
    ….
    | 28354 | 1 |
    | 28356 | 1 |
    +——-+———-+
    56 rows in set (0.02 sec)

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter rpamfil

    (@rpamfil)

    6 years, 1 month ago

    I have discovered that this query and bug is coming from events-calendar-pro, specifically events-calendar-pro/src/Tribe/Geo_Loc.php
    The relation OR needs to be changed to AND below.

    
	protected function get_venues_batch_geo_with_location_info() {
		$args                 = $this->shared_query_args_get_all_venue_ids;
		$args['post__not_in'] = $this->get_venues_batch_geo_to_ignore();
		$args['meta_query']   = array(
			'relation' => 'OR',
			array(
				'key'     => '_VenueAddress',
				'compare' => '!=',
				'value'   => '',
			),
...
    Thread Starter rpamfil

    (@rpamfil)

    6 years, 1 month ago

    the latest events calendar pro resolves this issue

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Serious error with MySQL query’ is closed to new replies.