[wp-trac] [WordPress Trac] #31071: post_mime_type related queries still slow on larger sites

[WordPress Trac]

#31071: post_mime_type related queries still slow on larger sites
-------------------------------------------------+-------------------------
 Reporter:  archon810                            |       Owner:  pento
     Type:  defect (bug)                         |      Status:  assigned
 Priority:  normal                               |   Milestone:  Future
Component:  Media                                |  Release
 Severity:  normal                               |     Version:  4.1
 Keywords:  dev-feedback reporter-feedback 2nd-  |  Resolution:
  opinion                                        |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by archon810):

 I'm a fan of multi-column indexes and use them whenever possible, but it's
 not so simple in complex applications because order matters in multi-
 column indexes, and crafting a single index is usually not possible unless
 all the queries are relatively simple and use the same fields in a similar
 manner.

 The art of index creation is very delicate and sometimes just flipping the
 order of two columns in an index can result in a 10x performance gain,
 even though you thought the original index was already pretty awesome.

 I wish a single multi-column index would be a panacea for all trouble
 related to query optimization, but you will soon find that it's not the
 case, otherwise everyone would just include that index by default.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/31071#comment:27>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform