Ticket #1659 (closed defect: fixed)

Opened 3 years ago

Last modified 2 years ago

(wp_)list_authors queries

Reported by: usayd Assigned to: ryan
Priority: normal Milestone: 2.2
Component: Optimization Version: 1.5.2
Severity: normal Keywords: has-patch commit
Cc:

Description

These two template tags:

wp_list_authors(<?php wp_list_authors(); ?>) and

list_authors (<?php list_authors(); ?>) (no matter what functions within them are called)

add an extra query to the database for each member registered on the blog - weather they are authors or not. On mine it adds 24 extra queries as shown below. This action should only be taken out in one database query to reduce the time for the page to load.

 [24] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '17' AND post_status = 'publish'
            [1] => 0.0088999271392822
        )

    [25] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '15' AND post_status = 'publish'
            [1] => 0.008976936340332
        )

    [26] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '4' AND post_status = 'publish'
            [1] => 0.009009838104248
        )

    [27] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '19' AND post_status = 'publish'
            [1] => 0.0088570117950439
        )

    [28] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '2' AND post_status = 'publish'
            [1] => 0.0090000629425049
        )

    [29] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '9' AND post_status = 'publish'
            [1] => 0.0089190006256104
        )

    [30] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '8' AND post_status = 'publish'
            [1] => 0.0088551044464111
        )

    [31] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '13' AND post_status = 'publish'
            [1] => 0.011569976806641
        )

    [32] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '12' AND post_status = 'publish'
            [1] => 0.010303974151611
        )

    [33] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '14' AND post_status = 'publish'
            [1] => 0.0098090171813965
        )

    [34] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '11' AND post_status = 'publish'
            [1] => 0.02720308303833
        )

    [35] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '7' AND post_status = 'publish'
            [1] => 0.0099828243255615
        )

    [36] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '18' AND post_status = 'publish'
            [1] => 0.011690855026245
        )

    [37] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '20' AND post_status = 'publish'
            [1] => 0.0088968276977539
        )

    [38] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '6' AND post_status = 'publish'
            [1] => 0.0089771747589111
        )

    [39] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '10' AND post_status = 'publish'
            [1] => 0.0090258121490479
        )

    [40] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '5' AND post_status = 'publish'
            [1] => 0.0089180469512939
        )

    [41] => Array
        (
            [0] => SELECT COUNT(*) FROM wp_posts WHERE post_author = '16' AND post_status = 'publish'
            [1] => 0.008929967880249
        )

Attachments

bug1659.diff (1.2 kB) - added by graeme on 11/07/05 04:42:29.
1659.diff (1.5 kB) - added by rob1n on 03/20/07 04:07:21.
Patch refreshed and refined.
1659.2.diff (1.4 kB) - added by rob1n on 03/21/07 03:12:05.
Lose the post_date_gmt test.

Change History

09/06/05 21:18:24 changed by markjaquith

  • owner changed from anonymous to markjaquith.
  • status changed from new to assigned.

Basically, what it does is this:

1. fetch all users 2. go through each, and count their posts

It should be possible to split the logic early on, and do a different query based on your $hide_empty setting. When $hide_empty is off, no prob, do the existing query and don't bother to filter later on. If $hide_empty is on, you need to do one big fancy query that joins the authors and the post tables, and grabs the count of the $wpdb->posts.ID column... might be a tad complicated, but I don't know of a better way.

11/07/05 04:42:18 changed by graeme

  • keywords changed from template tags to bg|2nd-opinion.
  • owner changed from markjaquith to graeme.
  • status changed from assigned to new.

Believe I've licked this. As always, there's likely a better way...

get_userdata() is still being called once per user, but (if the bug report is right) all that is being taken from the cache. Ideally the number of posts an author has (ah) authored should be part of their userdata, maybe? Anyway...

11/07/05 04:42:29 changed by graeme

  • attachment bug1659.diff added.

11/07/05 05:03:25 changed by graeme

  • keywords changed from bg|2nd-opinion to bg|2nd-opinion bg|has-patch.

03/10/07 05:55:35 changed by rob1n

  • owner changed from graeme to rob1n.
  • status changed from new to assigned.

03/12/07 00:42:00 changed by foolswisdom

  • milestone set to 2.3.

03/17/07 01:59:03 changed by rob1n

  • milestone changed from 2.3 to 2.2.

03/17/07 01:59:10 changed by rob1n

  • keywords deleted.

03/20/07 04:07:21 changed by rob1n

  • attachment 1659.diff added.

Patch refreshed and refined.

03/20/07 04:07:38 changed by rob1n

  • keywords set to has-patch.
  • status changed from assigned to new.

03/20/07 04:07:42 changed by rob1n

  • status changed from new to assigned.

03/20/07 04:09:08 changed by rob1n

  • keywords changed from has-patch to has-patch commit.

03/21/07 03:02:38 changed by ryan

Lose the test for post_date_gmt. We don't need that anymore.

03/21/07 03:12:05 changed by rob1n

  • attachment 1659.2.diff added.

Lose the post_date_gmt test.

03/25/07 17:01:15 changed by rob1n

  • owner changed from rob1n to ryan.
  • status changed from assigned to new.

Reassigning to ryan for commit.

03/29/07 01:36:33 changed by ryan

Let's try this one out. +1

03/29/07 01:39:08 changed by rob1n

  • status changed from new to closed.
  • resolution set to fixed.

(In [5135]) Speed (wp_)list_authors by consolidating some queries. Props graeme. fixes #1659