[wp-trac] [WordPress Trac] #39358: Media search speed has been dramatically reduced

[WordPress Trac]

#39358: Media search speed has been dramatically reduced
--------------------------+------------------------------
 Reporter:  merts         |       Owner:  joemcgill
     Type:  defect (bug)  |      Status:  reviewing
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Media         |     Version:  4.7
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+------------------------------

Comment (by dd32):

 > I think it's helpful to keep in mind a specific use case that the change
 tries to address. Assume someone uploads a file with a name like "large-
 monarch.jpg" but then renames the title of the attachment to something
 like "A Butterfly"

 I definitely understand the use-case and want to keep it, the options I
 presented were in a least-desired to most-desired order. Ditching it, or
 disabling it under certain conditions, is only really a last-ditch effort
 if there's no way for it to be performant - sorry if that wasn't clear
 from reading :)

 > b) I think that the current implementation (through a filter) allows
 large sites to turn this off if they so choose. That said, I'm more than
 happy to discuss ways the developer UX could be improved (e.g., moving
 away from the filter approach to some other configuration). In fact, we
 should pursue improvement here regardless.

 I don't think the ability to disable something through a filter solves
 this at all, if it was purely a developer thing, or something people ran
 into with plugins, it might be okay. But we're talking about the speed and
 functionality of the end-users interface - a slow interface is a bad
 experience.

 > c) I don't think exact matches are helpful here for reasons illustrated
 by the use case above.

 I hadn't considered that you were after wildcard matching within
 filenames, that makes it even harder as wildcard matches don't tend to
 utilise indexes. That suggests that we do indeed need to filter down the
 data using something stored within the posts table before it gets matched
 against postmeta.

 The suggestion of using the `guid` was never really going to fly, but was
 more to encourage thinking of how to go about it.
 An option that might be more realistic would be to still use the join, but
 to only match against a subsection of postmetas based on filtering on the
 posts table, for example the following would only hit a small percentage
 of postmeta:
 ` ( wp_posts.post_name LIKE '%zJv%' AND sq1.meta_value LIKE
 '%08W0zJv.jpg%' )`

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