[wp-hackers] Hacking Assignment: Paging by Days

[Ryan Boren]

On Mon, 2004-09-06 at 16:16 +0200, Sebastian Herp wrote:
> As much as i hate replying to myself, there was something wrong with the 
> code ...
> 
> Insert this in the "} elseif ($q['what_to_show'] == 'days') {"-case and 
> it will work (this time tested):
> 
>                                 $startrow = $q['posts_per_page'] * 
> (intval($page)-1);
>                                 $start_date = $wpdb->get_var("SELECT 
> max(post_date) FROM $wpdb->posts
>                                             GROUP BY year(post_date), 
> month(post_date), dayofmonth(post_date)
>                                             ORDER BY post_date DESC 
> LIMIT $startrow,1");
>                                 $endrow = $startrow + 
> $q['posts_per_page'] - 1;
>                                 $end_date = $wpdb->get_var("SELECT 
> min(post_date) FROM $wpdb->posts
>                                             GROUP BY year(post_date), 
> month(post_date), dayofmonth(post_date)
>                                             ORDER BY post_date DESC 
> LIMIT $endrow,1");
> 
>                                 if ($page > 1) {
>                                         $where .= " AND post_date >= 
> '$end_date' AND post_date <= '$start_date'";
>                                 } else {
>                                         $where .= " AND post_date >= 
> '$end_date'";
>                                 }
> 
> Adds another query, but is sexy as hell :-)

I like that better than my code.  Mine is rather brute force due to my
shallow knowledge of MySQL queries.  So, your code is in with a couple
of small tweaks.  Thanks.

> One thing though: this is the first time i tried out the cvs version. 
> While i can't say it is slow, i noticed that it takes 41 queries to 
> build up the mainpage compare to around 10 queries on a stock wordpress 
> 1.2. Why? This would slow down websites where the mysqlserver is not the 
> the webserver, too ...

I get 19 queries when paging by posts and 20 when paging by days.  I
think that is too much, 41 is definitely excessive.  It's time to go
over our queries and make sure we are using the caches where we should
be.  If anyone wants to do some profiling, that would be appreciated.

Ryan