WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#14593 closed defect (bug) (invalid)

wp_load_core_site_options() query optimization

Reported by: ellisgl Owned by:
Milestone: Priority: normal
Severity: trivial Version: 3.0.1
Component: Optimization Keywords: needs-testing has-patch
Focuses: Cc:

Description

wp_load_core_site_options() query doesn't use an index. This will trigger an entry in MySQL slow query log. It's more an

Suggested fix: Use "FORCE INDEX(meta_key)" to ensure an index is used.

Attachments (1)

ellisgl-forceindex.patch (1.0 KB) - added by ellisgl 4 years ago.
Patch

Download all attachments as: .zip

Change History (11)

ellisgl4 years ago

Patch

comment:1 scribu4 years ago

Your description seems to be truncated.

Also, you say FORCE INDEX(meta_key) in the description, but write FORCE INDEX(site_id).

Please clarify.

comment:2 follow-up: ellisgl4 years ago

ER
It's more of a minor annoyance for a small setup.

Suggested fix: Use "FORCE INDEX(site_id)" to ensure an index is used.

comment:3 in reply to: ↑ 2 ellisgl4 years ago

Replying to ellisgl:

ER
It's more of a minor annoyance for a small setup.

Suggested fix: Use "FORCE INDEX(site_id)" to ensure an index is used.

Of course if you have a lot of sites, then this could become an issue.

comment:4 follow-up: mrmist4 years ago

  • Keywords function query optimization removed

Has this been tested against a larger site? If the mysql optimizer is not using an index for a small table, it could be because it's determined that it's more efficient not to..

comment:5 in reply to: ↑ 4 ellisgl4 years ago

Replying to mrmist:

Has this been tested against a larger site? If the mysql optimizer is not using an index for a small table, it could be because it's determined that it's more efficient not to..

In our dev system, we currently have 7 sites defined.

comment:6 mrmist4 years ago

  • Keywords needs-testing has-patch added

OK. I guess it depends on what else goes into the multi-site meta table as to how big that particular table might get. What I'm trying to say is that for, say, 10 rows it's probably more expensive to do the lookup, and that's why the index is ignored in the first place. Some execution data from large and small sites with or without the patch might be worthwhile to decide whether the index should be forced or not..

Sadly I don't have a multi-site install to test on, big or small.

comment:7 ellisgl4 years ago

My finding were that 40 rows were scanned before, after 39. I need to setup a large system. This would have to be next week.

comment:8 ellisgl4 years ago

Here's a question, how do get more than 1 site id in the 'sitemeta' table the normal way. I add sites, and it doesn't add anything.. =/ I could cheat and run a script that create a ton of copies.

comment:9 ellisgl4 years ago

  • Resolution set to invalid
  • Status changed from new to closed

Took my own advise and cheated. If there is more than 1 site_id, then the optimizer does kick in. Setting ticket to "invalid". Sorry for wasting your time.

comment:10 scribu4 years ago

  • Milestone Awaiting Review deleted
Note: See TracTickets for help on using tickets.