[wp-trac] Re: [WordPress Trac] #7415: "using filesort" in default
install
WordPress Trac
wp-trac at lists.automattic.com
Sun Feb 22 12:26:12 GMT 2009
#7415: "using filesort" in default install
--------------------------+-------------------------------------------------
Reporter: dbuser123 | Owner: anonymous
Type: defect (bug) | Status: new
Priority: high | Milestone: 2.8
Component: Optimization | Version: 2.7
Severity: major | Keywords: database, optimization, slow queries, filesort
--------------------------+-------------------------------------------------
Comment(by rawalex):
Replying to [comment:33 mrmist]:
> Replying to [comment:32 rawalex]:
> > Because of the overhead of calc_found_rows, wouldn't it just be better
to make the first query full without limits and count it, and control the
number of items displayed elsewhere? Right now you are doing two full
queries, which would be more intensive, no?
>
> The mysql docs say
>
>
> {{{
> If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how
many rows are in the full result set. However, this is faster than running
the query again without LIMIT, because the result set need not be sent to
the client.
> }}}
>
>
> And I'm tempted to agree. If you have a massive results set the last
thing you would want to do is return the entire thing to the client.
The point is run the query (normally without the "count" and then just do
a mysql_num_rows() on the result. That means you only do the query once,
and then ask it how many (which is a very low load request). The limit is
meaningless in the current query (because the entire database must be
considered), so the limit would be just as well done in software as put
here.
Moving also to replace the year() and month() parts of the queries with
actual start and end dates would also improve the query dramatically.
--
Ticket URL: <http://core.trac.wordpress.org/ticket/7415#comment:34>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list