[wp-trac] [WordPress Trac] #24837: querying optimization for category AND searching

[WordPress Trac]

#24837: querying optimization for category AND searching
-------------------------------------+------------------------------
 Reporter:  robertv123               |       Owner:
     Type:  enhancement              |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Taxonomy                 |     Version:  3.6
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+------------------------------

Comment (by robertv123):

 There may be other ways of doing this that gets mysql to handle this
 internally but I have not as yet found it yet. If someone knows a sql
 super star I'd like to hear alternatives, my mysql is quite basic.

 Portability comes into question as wordpress uses a wide range of quries,
 this pattern optimizes sub queries works across the board for earlier 4.x
 mysql as well.

 These types of quires have been on the mysql todo list for quite some time
 now, a decade or more. Latest promise is mysql 6.0, there is some that has
 been completed for 5.6 but it's far from being done.

 Word press will likely still be run on plenty of 5.x mysql installs for
 quite some time so any sub queries in the code are land mines for users
 who happen to create a larger data set. In my case it was 120000 records
 in wp_term_relationships , 60000 posts.

 For low end virtual machines or shared hosting the threshold would be
 lower.

 Here is more info on sub queries
 http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-
 part-3-subqueries/

--
Ticket URL: <http://core.trac.wordpress.org/ticket/24837#comment:7>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software