List your MySQL Tables According to Size or Row Count

If you’ve ever been debugging a large Multisite installation that’s having MySQL memory issues you know how maddening it can be. Sometimes you can turn on slow logging and find the source of the issue. But sometimes it’s not just about slow queries, it’s about the shear size of the tables and the number of queries being run against them. One trick is to generate a list of all the tables on the site and sort them by size. This will help you narrow down trouble spots.

USE INFORMATION_SCHEMA; 
SELECT table_name,table_rows,data_length FROM TABLES where table_schema = 'DBNAME' AND table_rows > 0 ORDER BY data_length DESC LIMIT 0,50;

This will produce a list like the following (see below). You can adjust the sort order and limit based on your needs. This query isn’t all that helpful in cases of small installs. But in an installation with 500+ tables it can help you narrow down the issue quickly. WARNING: this query can take a long time to run depending on the number of databases on your server and the number of tables in the Dbs.

table_nametable_rowsdata_length
wp_options12565536
wp_blogs116384
wp_sitemeta2516384
wp_site116384
wp_posts316384
wp_postmeta116384
wp_users216384
wp_comments116384
wp_usermeta3116384
wp_links7560
wp_term_relationships8168
wp_term_taxonomy284
wp_terms272

The Simplest WordPress User Access Log Ever

Those of use who develop using pods often find we use it for everything. So here’s a quick tip on using PodsCMS to create a custom user access log.

Step 1: Set up the Pod

I’m assuming you’ve already installed/activate both the PodsCMS and Pods UI plugins. If not, please do so before starting.

Create a new Pod called “logins”.

By default each pod is created with a name and slug field. We’re going to use the name field but you can delete the slug field.

Then you’ll need to create a field for “date”. Of course, Pods stores the date any entry is created in a field called “created” which you can access from within Pods Templates. But it still makes sense to have a date field in the Pod itself, if nothing else for the sake of a clear data model.

So once you have added the date field your Pod will look like this:

Step 2: Add function

Now just add the following code to your functions.php file.

add_action('set_logged_in_cookie','mpv_add_access_log_entry');
function mpv_add_access_log_entry($user) {
$user = explode('|',$user);
$log = new PodAPI(); 
$params = array('datatype'=>'logins');
$params['columns'] = array('name'=>$user[0],'date'=>date('Y-m-d H:i:s'));
$log->save_pod_item(pods_sanitize($params));
}

This function uses the Pod API to insert a row in the logins table. Alternatively you can use the $wpdb class and do something like this:

global $wpdb;
$wpdb->insert($wpdb->prefix.'pod_tbl_logins',array('name'=> $user[0],'date'=> date('Y-m-d H:i:s')));

The only trouble with going this route is that in order to use the pods admin interface to manage the data, you’ll also need to add a row to the wp_pod table. This will change with Pods 2.0 so there’s no need to demonstrate. But I strongly recommend using the PodsAPI class as it will make sure to implement best practices and in 2.0 it will use the $wpdb class anyway.

So that’s it. To create an exportable report of the logins just install the “Exports and Reports” plugin. Or you can use PodsUI to create a custom interface.

Have fun.

WordPress 3.1: Query Multiple Custom Fields

So I just implemented for the first time the new WordPress ‘meta_query’ structure that was added to WordPress 3.1. I must say, it works swimmingly. Here’s my query:

-> To look up events that start after the current date and then order those events ascending by starting date:

$tday = date('Y-m-d H:i:s');
$args = array(
'post_type' => 'events',
'showposts' => 1,
'meta_query'=> array(
array(
'key'=>'simplr_starts',
'value'=> $tday,
'compare' => '>'
)
),
'meta_key'=>'simplr_starts',
'orderby'=>'meta_value',
'order'=>'ASC'
);
$events = new WP_query($args); 
// run the rest of the loop as usual. 

Check out Scribu for more info.