WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 8 years ago

Last modified 4 years ago

#20134 closed enhancement (wontfix)

Performance Problems with Complex Meta Queries

Reported by: dominicp Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.3.1
Component: Query Keywords:
Focuses: Cc:
PR Number:

Description

I have noticed that when I have several criterion on a WP_Query meta query it can start to drastically impact performance. I don't know if this is just an unavoidable consequence of using the key/value data structure that WordPress does, but I wanted to bring it to the dev's attention now as it seems like 3.4 is going to involve some serious work on the Query component.

I have outlined a specific example of the problem in some detail here: http://stackoverflow.com/q/8893160/931860, and I can definitely provide more details if someone feels they would be relevant.

Change History (6)

#1 @scribu
8 years ago

The only thing we can do here is add an index to the meta_value column.

Since meta_value is of type LONGTEXT, the index needs to be limited to a certain length, which is up for debate.

#2 @dominicp
8 years ago

I'm not a MySQL expert, but someone said on the stackoverflow question that that might not help (http://stackoverflow.com/questions/8893160/mysql-wordpress-slow-query-when-using-in-statements/8893207#comment11120031_8893207).

I tested out creating a 10 character index, and I didn't see any impact on performance in my particular case.

#3 @scribu
8 years ago

  • Keywords dev-feedback removed
  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from new to closed

Well, the table layout is not going to change, so that's that. You might consider using Pods or some other plugin which uses custom tables.

In general, when you find yourself doing a lot of IN queries on meta, consider using a taxonomy instead.

#4 @dominicp
8 years ago

Thanks for the helpful tip. I will definitely look into using taxonomies instead of post meta.

I agree that the table layout shouldn't change. I just wanted to bring up the performance issues I was seeing in case it was something that could be addressed within the current table layout. Thanks for taking a look.

#5 @dominicp
8 years ago

In case it will help someone, I wound up using the STRAIGHT_JOIN MySQL optimizer hint on my complex meta query (in addition to taking scribu's advice on switching to taxonomies), and the performance was dramatically improved.

I don't know why, and it might be a terrible idea in the long run, but I figured I'd post this in case anyone else can benefit. I've documented the results more fully on the StackOverflow question I referenced initially.

This ticket was mentioned in Slack in #core by sergey. View the logs.


4 years ago

Note: See TracTickets for help on using tickets.