WordPress Query Monitor Primer Tutorial

WordPress Query Monitor Primer Tutorial

Our WordPress blog at RJM Programming uses a MySql database, and its PHP web server source queries that database to find information. That SQL used is the MySql variety of SQL, but there is not that much difference between MySql SQL or the T-SQL of MS-SQL or Oracle SQL or PostGRE SQL or Access SQL or Advantage SQL when it comes down to the basics of DML statements for …

• SELECT
• INSERT
• UPDATE
• DELETE

… and with that “SELECT” we hone in on today regarding “SELECT” SQL statements …

• “SELECT” column list
• FROM part of statement including its JOIN parts
• WHERE part of statement
• GROUP BY part of statement
• ORDER BY part of statement

When we created WordPress Blog MySql Database PHP Code Tutorial we got in “under the hood” of WordPress towards its “backend” MySql database parts, to the point where there was a place in the code where we could piece together what the MySql “SELECT” SQL DML statement was, and this is where we intervene today …


$tmp = str_replace("ORDER BY post_title ASC AND", " AND ", str_replace("ORDER BY post_title DESC AND", " AND ", str_replace("1=1 post_title AND", "1=1 AND", str_replace("post_title AND wp_posts.post_type", " AND wp_posts.post_type", " SELECT $distinct $wpdb->posts.id FROM $wpdb->posts $join WHERE 1=1 $where $groupby ")))) . localmaybe($orderby, " ") . " $limits";
if (file_exists("localsearch_" . server_remote_addr() . ".qsinterest")) { // user interest regarding SELECT query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".qsinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "SELECT ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".qsinterest", $tmp);
}
if (file_exists("localsearch_" . server_remote_addr() . ".qiinterest")) { // user interest regarding INSERT query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".qiinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "INSERT ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".qiinterest", $tmp);
}
if (file_exists("localsearch_" . server_remote_addr() . ".quinterest")) { // user interest regarding UPDATE query
$qinterest=@file_get_contents("localsearch_" . server_remote_addr() . ".quinterest");
if (strlen($qinterest) < 3 && strpos($tmp, "UPDATE ") !== false) file_put_contents("localsearch_" . server_remote_addr() . ".quinterest", $tmp);
}


… with our idea to show you the MySql SQL involved at any given query point.

So we allow today’s live run to operate as a separate web browser window monitoring the work you do in other windows for that blog where a “SELECT” SQL query happens, and you’ve clicked the button to say you are interested. Here is its PHP source code you could call query_monitor.php that should be placed at the document root of the WordPress blog and is specific to the Twenty Ten theme we use with our WordPress blog in the sense that it is the TwentyTen’s theme’s wp-includes/query.php that is changed with the code snippet you see above.

Previous relevant WordPress Blog MySql Database PHP Code Tutorial is shown below.

WordPress Blog MySql Database PHP Code Tutorial

Were you here yesterday for the WordPress Blog Alphabetical Search Follow Up Tutorial? Reading it, you may have wondered about why the problem was not handled from the “backroom up”, if you see what we mean. We also wonder about this with our WordPress 4.1.1 version of blog posting called WordPress Blog MySql Database PHP Code Tutorial. By that we mean, the word “backroom” is a metaphor for “database”, and we wanted to use a metaphor … okay? Zsseeeeesh!

It is true that you can do this, but would recommend following WordPress Codex ideas, and will suggest a link here that you can work “in and out from” … it’s a metaphor … ok?! … Zsssseeeeeeeeessssh!

But seriously, if you want a bit of DIY advice regarding the interface WordPress has, PHP-code-wise, to the MySql database, we have some suggestions …

• Apache/PHP/MySql web hosting is likely to have an interface to the MySql database via phpMyAdmin … or should we say the marvellous, wonderful, stupendous phpMyAdmin? … getting to know this product is very useful
• WordPress query.php code in wp-includes directory is a place to look for some of the (MySql) SQL queries and their implementation

We concentrate heavily, here, on changes to the WordPress Blog TwentyTen theme’s header.php, but we have also made changes to this query.php in the past. For this we followed the wonderful advice from this useful link for which we give hearty thanks. The query.php code changed as per …


$ diff query.php_old query.php
2412c2412,2426
< $this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
---
> // Was ...
> // $this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> // Now 20/11/2014 ... thanks to http://www.web0.at/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination/
> $tmp = " SELECT $distinct $wpdb->posts.id FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> $tmp_q = $wpdb->get_col($tmp);
> $tmp_ids = implode($tmp_q,',');
> $sqlString = " SELECT $distinct $fields FROM $wpdb->posts $join WHERE $wpdb->posts.id IN ($tmp_ids) $groupby $orderby ";
>
> $sqlString2 = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
>
> $this->request = $sqlString;
> // ... end of new code
>
2435c2449,2453
< $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );
---
> // Was ...
> // $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( 'SELECT FOUND_ROWS()', &$this ) );
> // Now ... 20/11/2014 ... thanks to http://www.web0.at/sql_calc_found_rows-slow-in-wordpress-solution-with-working-pageination/
> $found_posts_query = apply_filters_ref_array( 'found_posts_query', array( "SELECT count($distinct $wpdb->posts.id) FROM $wpdb->posts $join WHERE 1=1 $where", &$this ) );
> // ... end of change


So what’s the story? … the tale? … it’s a simile … Zsssssseeeeeeeeeeeessssssh!

Well, it’s that with all things to do with the database, proceed carefully, and read up on it, especially in WordPress Codex, before proceeding, perhaps even backing up the database prior to any major changes you make (in PHP) to MySql (in WordPress database). For DIY projects that are readonly ones, bear in mind that there is less danger to what you do. Adjusting SQL SELECT statements that stand alone, and are not part of an ongoing procedure that eventually does any UPDATE nor INSERT nor DELETE to the database, could be good candidates for the use of query.php to attempt a solution of your own to an issue in WordPress for its MySql database, using PHP code.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.