Make WordPress Core

Opened 11 years ago

Closed 11 years ago

#26281 closed enhancement (duplicate)

Optimizing meta_query generated SQL? -> 9s to 20ms

Reported by: vprat's profile vprat Owned by:
Milestone: Priority: normal
Severity: major Version: 3.8
Component: Query Keywords: has-patch
Focuses: Cc:

Description

I have a problem in a plugin of mine which uses post meta to store ownership of a post. Basically, my issue is that the WP_Meta_Query class generates one JOIN per meta_query query. I don't understand why we cannot us the same JOIN for all queries.

Of course, when using more than 5 or 6 queries, the query takes way too much time to complete and plugin fails to get the posts.

E.g.: this is what I would do to get the posts that belong to either users 5, 6, 8 and 10 (In the real plugin, the compare is always LIKE and value looks like '%|usr_5|%' to solve some cases for advanced ownership control):

'meta_query' => array( 
  'relation' => 'OR',
  array(
    'key'      => 'owner',
    'value'    => 5,
    'compare'  => '='
  ),
  array(
    'key'      => 'owner',
    'value'    => 6,
    'compare'  => '='
  ),
  array(
    'key'      => 'owner',
    'value'    => 8,
    'compare'  => '='
  ),
  array(
    'key'      => 'owner',
    'value'    => 10,
    'compare'  => '='
  )
)

That above generates a SQL query that has 4 JOIN statements (mt1 to mt4) and in the WHERE clause, is using each JOIN for a comparison. Something like:

mt1.meta_key = 5 OR mt2.meta_key = 6 OR mt3.meta_key = 8 OR mt4.meta_key = 10

What is the point of that??! All of this could be done using a single JOIN and a WHERE clause like:

mt1.meta_key = 5 OR mt1.meta_key = 6 OR mt1.meta_key = 8 OR mt1.meta_key = 10

Is there something I am missing? Is that multiple JOIN there to take care of some comparison types?

Attachments (1)

meta.php.patch (2.6 KB) - added by vprat 11 years ago.
Patch to avoid multiple JOINs for the meta query

Download all attachments as: .zip

Change History (6)

@vprat
11 years ago

Patch to avoid multiple JOINs for the meta query

#1 @vprat
11 years ago

I don't know if there are automated tests that already exist for that class, but I'd love to know if my patch is causing something to break. We may be able to isolate such obvious cases to optimize most queries and let the other queries be processed with all required JOINs.

#2 follow-up: @nacin
11 years ago

  • Component changed from General to Query

Hi vprat. Thanks very much for the bug report and patch. I think this overlaps with another ticket, but I can't locate it at the moment. Let me see if someone else can. If we can combine the research and work in both, we should be able to come up with a well-tested candidate for core.

There are some nascent tests for that class here:
https://core.trac.wordpress.org/browser/trunk/tests/phpunit/tests/meta/query.php

#3 follow-up: @vprat
11 years ago

Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation.

So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.

The ticket you are looking for is https://core.trac.wordpress.org/ticket/24093 and has a patch too (probably a better one).

#4 in reply to: ↑ 2 @vprat
11 years ago

Replying to nacin:

I think this overlaps with another ticket, but I can't locate it at the moment.

Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation. So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.

The ticket you are looking for is https://core.trac.wordpress.org/ticket/24093 and has a patch too (probably a better one).

#5 in reply to: ↑ 3 @nacin
11 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Replying to vprat:

Apparently, the optimisation would not be valid for AND relation. However, this is perfectly right for OR relation.

So I guess just writing a special case for the OR relationship (in which case just use a single JOIN) would do the trick.

Indeed, on all counts! Feel free to jump in on #24093 if you can help.

Note: See TracTickets for help on using tickets.