Skip to content

Instantly share code, notes, and snippets.

@stathisg
Last active August 29, 2015 14:04
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save stathisg/88d5194f4ad6f8061a4e to your computer and use it in GitHub Desktop.
Profiling the performance of a few queries (based on the following discussion: http://www.freestuff.gr/forums/viewtopic.php?t=68286)
<?php
set_time_limit(0);
$db_host = ''; //Database hostname
$db_username = ''; //Database username
$db_password = ''; //Database password
$db_name = ''; //Database name
$db_table_name = ''; //Database table name
function create_table() {
$st = $GLOBALS['pdo']->prepare("CREATE TABLE IF NOT EXISTS `{$GLOBALS['db_table_name']}` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`value` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
$st->execute();
}
function fill_with_data($rows_count, $max_value) {
$st = $GLOBALS['pdo']->prepare("INSERT INTO {$GLOBALS['db_table_name']} (`title`, `value`) VALUES (:title, :value)");
$st->bindParam(':title', $title);
$st->bindParam(':value', $value);
for($i=0; $i < $rows_count; $i++) {
$title = "title $i";
$value = rand(0, $max_value);
$st->execute();
}
}
function enable_profiling() {
$st = $GLOBALS['pdo']->prepare("set profiling=1");
$st->execute();
}
function disable_profiling() {
$st = $GLOBALS['pdo']->prepare("set profiling=0");
$st->execute();
}
function select_max() {
$st = $GLOBALS['pdo']->prepare("SELECT MAX(`value`) as `max` FROM {$GLOBALS['db_table_name']}");
$st->execute();
}
function select_count() {
$st = $GLOBALS['pdo']->prepare("SELECT COUNT(*) FROM {$GLOBALS['db_table_name']}");
$st->execute();
}
function select_max_count() {
$st = $GLOBALS['pdo']->prepare("SELECT MAX(`value`) as `max`, COUNT(*) FROM {$GLOBALS['db_table_name']}");
$st->execute();
}
function select_order_by() {
$st = $GLOBALS['pdo']->prepare("SELECT `value` FROM {$GLOBALS['db_table_name']} ORDER BY `value` DESC");
$st->execute();
}
function select_all_order_by() {
$st = $GLOBALS['pdo']->prepare("SELECT * FROM {$GLOBALS['db_table_name']} ORDER BY `value` DESC");
$st->execute();
}
function show_profiles() {
$st = $GLOBALS['pdo']->prepare("SHOW PROFILES");
$st->execute();
while($row = $st->fetch()) {
echo "Query: $row->Query <br />";
echo "Duration: $row->Duration <br /><br />";
}
}
try {
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_username, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
//create_table();
//fill_with_data(10000, 1000);
enable_profiling();
select_max();
select_count();
select_max_count();
select_order_by();
select_all_order_by();
show_profiles();
disable_profiling();
} catch(PDOException $e) {
echo $e->getMessage();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment