WordPress website 10W+ data, database query optimization.

When WordPress queries the post list, it will also query the number of articles by default.

Using this method are: get_posts, query_posts and WP_Query.

get_posts no longer uses SQL_CALC_FOUND_ROWS in 4.6.1+, but query_posts and WP_Query still use it, so it needs to be optimized.

The specific statements are as follows: SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish‘ ) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 SELECT FOUND_ROWS()

This will not cause any problems when the website data volume is small.

But when the number of posts reaches 100,000+, this will be a slow query that will appear.

Home page, categories, tags, and search pages, as long as these functions are used, the SQL_CALC_FOUND_ROWS method will be used.

How to solve it?

Method 1:

Disable SQL_CALC_FOUND_ROWS completely

Just put it in the functions.php file: add_action(‘pre_get_posts’, ‘wndt_post_filter’); function wndt_post_filter($query) { if (is_admin() or !$query->is_main_query()) { return $query; } // Disable query SQL_CALC_FOUND_ROWS $query->set(‘no_found_rows’, true); }

Method 2:

If you still need to query the number of articles, use the more efficient EXPLAIN method instead of SQL_CALC_FOUND_ROWS

Disable the SQL_CALC_FOUND_ROWS usage in a more efficient way.

Here we use EXPLAIN method

The specific code is as follows, which can be placed in the functions.php file: if ( ! function_exists( ‘maizi_set_no_found_rows’ ) ) { /** * Set the ‘no_found_rows’ attribute of WP_Query to true and disable SQL_CALC_FOUND_ROWS * * @param WP_Query $wp_query WP_Query instance * @return void */ function maizi_set_no_found_rows(/WP_Query $wp_query) { $wp_query->set(‘no_found_rows’, true); } } add_filter(‘pre_get_posts’, ‘maizi_set_no_found_rows’, 10, 1); if ( ! function_exists( ‘maizi_set_found_posts’ ) ) { /** * Refactor using EXPLAIN method */ function maizi_set_found_posts($clauses, /WP_Query $wp_query) { // Don’t proceed if it’s a singular page. if ($wp_query->is_singular()) { return $clauses; } global $wpdb; $where = isset($clauses[‘where’]) ? $clauses[‘where’] : ”; $join = isset($clauses[‘join’]) ? $clauses[‘join’] : ”; $distinct = isset($clauses[‘distinct’]) ? $clauses[‘distinct’] : ”; $wp_query->found_posts = (int)$wpdb->get_row(“EXPLAIN SELECT $distinct * FROM {$wpdb->posts} $join WHERE 1=1 $where”)–>rows; $posts_per_page = (!empty($wp_query->query_vars[‘posts_per_page’]) ? absint($wp_query->query_vars[‘posts_per_page’]) : absint(get_option(‘posts_per_page’))); $wp_query->max_num_pages = ceil($wp_query->found_posts / $posts_per_page); return $clauses; } } add_filter(‘posts_clauses’, ‘maizi_set_found_posts’, 10, 2);

Why use EXPLAIN instead of count(*)?

select count(*) is the most commonly used method in MySQL to count the number of record rows.

The count method can return the exact number of rows in the table. Each time it is executed, a full table scan will be performed.

To avoid inaccurate results caused by delete and insert performed by other connections.

This is a good thing under certain indexes, but if there is a primary key in the table, count(*) will be very slow, especially in large tables with more than ten million records.

It will be much faster if you use the explain command, because explain does not actually execute the query, but the number of rows estimated by the query optimizer.

When tested in a table with 15 million records, using select count(*) took 15 seconds, while using explain took 0.08 seconds.

The difference between the two is almost 200 times (the first execution will be slightly slower, about 3 seconds).

The following is the explanation method: mysql> explain select * from posts; +——-+————-+————-+————+——+——————+——+————+——+————-+————-+ ——-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——-+————-+————-+————+——+——————+——+————+——+————-+————-+ ——-+ | 1 | SIMPLE | posts | NULL | ALL | NULL | NULL | NULL | NULL | 12596096 | 100.00 | NULL | +——-+————-+————-+————+——+——————+——+————+——+————-+————-+ ——-+ 1 row in set, 1 warning (0.08 sec)

Note that select * is used here, not select count(*).

select * will return a row of data, including the estimated number of rows. In PHP, we fetch() and then pass $result[‘rows’] to get the estimated value.

select count(*) will have a line Select tables optimized away in the extra, and will not get the function estimate.

So, in situations where the accuracy of the data is not high, but the speed is very stringent, it is absolutely necessary to use this estimate instead.

You can also use the following sentence, the result is exactly the same as explain: select TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME=‘posts’; +————+ | TABLE_ROWS | +————+ | 12596096 | +————+ 1 row in set (0.04 sec)

Choose one according to the actual situation, they are all the same thing.

Recommended site search: virtual host application, vps server rental, free php space application server rental US high defense, Hong Kong vps host rental, US imitation brand space, permanent free foreign accelerator, vps dynamic ip, free asp space , virtual host service provider,