You have a schema containing lots of MyISAM tables that may possibly be stale due to code no longer updating them. You can rank them by date of most recent update.

mysql -D PriceComparison -NBe"show table status like '%'" \
| \
cut -f1,2,11,12 \
| \
sort -k 5,6

I omitted a grep MyISAM because the InnoDB tables will be grouped together with NULL dates so aren’t a problem. The sneaky problem to avoid is that sort sees a datetime field as being two fields. To sort the list by creation date you would use sort -k 3,4.

For reporting-style stats we often need to round down ‘now’ to the nearest 5 mins. For instance, my current project requires the code to identify the current ‘rolling twenty minutes’ with such rounding. An easy way to do this that works even in MySQL v4.0 is the following.

SELECT FROM_UNIXTIME(300 * FLOOR( UNIX_TIMESTAMP()/300 ))

which gives the most recently-passed 5-min datetime, taking 13:52:50 down to 13:50:00.
Of course, if you need rounding to the nearest 15 mins then the 300 would be changed to 900.

If your tables are all using InnoDB then you can get a snapshot of the master with minimal locking using

schemata="one two three"
mysqldump --opt --no-data --routines --skip-triggers \
  --databases $schemata >schema__all.sql
mysqldump --opt --no-create-info --skip-routines --triggers \
  --flush-logs --single-transaction --master-data=2 \
  --databases $schemata >data__all.sql

Otherwise, if you have some non-transactional tables (eg MyISAM) then omit single-transaction and use table locking.
A comment in the dump file will tell you what settings to use for master.info but you’d be able to guess it anyway (thanks to flush-logs).

change master to master_log_file='..', master_log_pos=..  # probably 107

Very useful summary of changes required at pythian.com

Start the server with

mysqld_safe –skip-grant-tables &

From http://perl.apache.org/docs/1.0/guide/performance.html#Work_With_Databases


my @select_fields = qw(a b c);
# create a list of cols values
my @cols = ();
@cols[0..$#select_fields] = ();
$sth = $dbh->prepare($do_sql);
$sth->execute;
# Bind perl variables to columns.
$sth->bind_columns(undef,\(@cols));
print q{<table border="0">};
while($sth->fetch) {
print q{<tr>},
map(qq{<td>$_</td>}, @cols),
q{</tr>};
}
print q{</table>};


“As a bonus, I wanted to write a single sub that flexibly processes any query. It would accept conditions, a call-back closure sub, select fields and restrictions.”

# Usage:
# $o->dump(\%conditions,\&callback_closure,\@select_fields,@restrictions);
#
sub dump{
my $self = shift;
my %param = %{+shift}; # dereference hash
my $rsub = shift;
my @select_fields = @{+shift}; # dereference list
my @restrict = shift || '';

# create a list of cols values
my @cols = ();
@cols[0..$#select_fields] = ();

my $do_sql = ”;
my @where = ();

# make a @where list
map { push @where, “$_=\’$param{$_}\'” if $param{$_};} keys %param;

# prepare the sql statement
$do_sql = “SELECT “;
$do_sql .= join(” “, @restrict) if @restrict; # append restriction list
$do_sql .= ” ” .join(“,”, @select_fields) ; # append select list
$do_sql .= ” FROM $DBConfig{TABLE} “; # from table

# we will not add the WHERE clause if @where is empty
$do_sql .= ” WHERE ” . join ” AND “, @where if @where;

print “SQL: $do_sql \n” if $debug;

$dbh->{RaiseError} = 1; # do this, or check every call for errors
$sth = $dbh->prepare($do_sql);
$sth->execute;
# Bind perl variables to columns.
$sth->bind_columns(undef,\(@cols));
while($sth->fetch) {
&$rsub(@cols);
}
# print the tail or “no records found” message
# according to the previous calls
&$rsub();

} # end of sub dump


“Now a callback closure sub can do lots of things. We need a closure to know what stage are we in: header, body or tail. For example, we want a callback closure for formatting the rows to print:”

my $rsub = eval {
# make a copy of @fields list, since it might go
# out of scope when this closure is called
my @fields = @fields;
my @query_fields = qw(user dir tool act); # no date field!!!
my $header = 0;
my $tail = 0;
my $counter = 0;
my %cols = (); # columns name=> value hash

# Closure with the following behavior:
# 1. Header’s code will be executed on the first call only and
# if @_ was set
# 2. Row’s printing code will be executed on every call with @_ set
# 3. Tail’s code will be executed only if Header’s code was
# printed and @_ isn’t set
# 4. “No record found” code will be executed if Header’s code
# wasn’t executed

sub {
# Header
if (@_ and !$header){
print “<TABLE>\n”;
print $q->Tr(map{ $q->td($_) } @fields );
$header = 1;
}

# Body
if (@_) {
print $q->Tr(map{$q->td($_)} @_ );
$counter++;
return;
}

# Tail, will be printed only at the end
if ($header and !($tail or @_)){
print “</TABLE>\n $counter records found”;
$tail = 1;
return;
}

# No record found
unless ($header){
print $q->p($q->center($q->b(“No record was found!\n”)));
}

} # end of sub {}
}; # end of my $rsub = eval {


“Here is the updated code fragment which employs this [tag substitution] optimization:”
# ...
my $dbh = DBI->connect('dbi:Oracle:host', 'user', 'pass')
|| die $DBI::errstr;

my $baz = $r->param('baz');

eval {
my $sth = $dbh->prepare(qq{
SELECT foo
FROM bar
WHERE baz = :baz
});
$sth->bind_param(':baz', $baz);
$sth->execute;

while (my @row = $sth->fetchrow_array) {
# do HTML stuff
}

$sth->finish;

my $sph = $dbh->prepare(qq{
BEGIN
my_procedure(
arg_in => :baz
);
END;
});
$sph->bind_param(':baz', $baz);
$sph->execute;
$sph->finish;

$dbh->commit;
};
if ($@) {
$dbh->rollback;
}
# ...