The original author of this topic is
effemmess but after the hack this topic was lost. I'm just republishing it.
Update for better understanding
(same code as before) (14.12.2003)
Hallo allerseits!
Hello to all!
Nach langer Testerei ist es mir gelungen, die Zufallsbildfunktion zu optimieren und damit entscheidend zu beschleunigen.
Zeitmessungen auf meinem Produktionsserver haben eine 3-4-fache Beschleunigung dieser Funktion ergeben.
------------------------------------------------------------------------
Hello all,
after a long test-time, I?ve succeded to increase the speed for the random-image-function. It is now 3-4 times faster than before.
========================================
Mindestvoraussetzungen / minimal configuration:
- 4images 1.7
- in .../includes/constants.php:
define('SHOW_RANDOM_IMAGE', 1);
define('SHOW_RANDOM_CAT_IMAGE', 1);
- MySQL 3.23.xx oder neuer/ or newer
Was ist also zu tun / what to do:
1. f?e nachfolgenden Varianten ersetze in .../includes/functions.php dies
for all following variants replace this in .../includes/functions.php
function get_random_image_cache() {
global $site_db, $cat_cache, $total_images;
$random_image_cache = array();
$cat_id_sql = get_auth_cat_sql("auth_viewcat", "NOTIN");
if (SHOW_RANDOM_CAT_IMAGE) {
$sql = "SELECT DISTINCT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
ORDER BY RAND()";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$random_image_cache[$row['cat_id']] = $row;
}
}
else {
if (empty($total_images)) {
$sql = "SELECT COUNT(*) as total_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)";
$row = $site_db->query_firstrow($sql);
$total_images = $row['total_images'];
}
if (empty($total_images)) {
return $random_image_cache;
}
mt_srand((double)microtime() * 1000000);
$number = ($total_images > 1) ? mt_rand(0, $total_images - 1) : 0;
$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
LIMIT $number, 1";
$random_image_cache[0] = $site_db->query_firstrow($sql);
}
return $random_image_cache;
}
mit einer der folgenden Varianten:
with one of the following variants:
a) optimierter Code
optimized code
function get_random_image_cache() {
global $site_db, $cat_cache, $total_images, $session_info;
$random_image_cache = array();
$cat_id_sql = get_auth_cat_sql("auth_viewcat", "NOTIN");
mt_srand((double)microtime() * 1000000);
if (SHOW_RANDOM_CAT_IMAGE) {
$temptab = (!empty($session_info['session_id'])) ? "tab_".$session_info['session_id'] : "tab_".mt_rand(0,1000000);
$sql = "
CREATE TEMPORARY TABLE ".$temptab." TYPE = HEAP
SELECT image_id, cat_id
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)
ORDER BY RAND()";
$result = $site_db->query($sql);
$sql = "
SELECT DISTINCT t.image_id, t.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".$temptab." AS t
LEFT JOIN ".CATEGORIES_TABLE." AS c ON t.cat_id=c.cat_id
LEFT JOIN ".IMAGES_TABLE." AS i ON t.image_id=i.image_id
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
GROUP BY t.cat_id";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$random_image_cache[$row['cat_id']] = $row;
}
$sql = "
DROP TABLE ".$temptab;
$result = $site_db->query($sql);
}
else {
if (empty($total_images)) {
$sql = "SELECT COUNT(*) as total_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)";
$row = $site_db->query_firstrow($sql);
$total_images = $row['total_images'];
}
if (empty($total_images)) {
return $random_image_cache;
}
$number = ($total_images > 1) ? mt_rand(0, $total_images - 1) : 0;
$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
LIMIT $number, 1";
$random_image_cache[0] = $site_db->query_firstrow($sql);
}
return $random_image_cache;
}
b) optimierter Code mit Zeitmessung f?ins
optimized code for measuring times for admins
function get_random_image_cache() {
global $site_db, $cat_cache, $total_images, $session_info, $user_info;
$random_image_cache = array();
$cat_id_sql = get_auth_cat_sql("auth_viewcat", "NOTIN");
if ($user_info['user_level'] >=ADMIN){
$time1 = getmicrotime();
}
mt_srand((double)microtime() * 1000000);
if (SHOW_RANDOM_CAT_IMAGE) {
$temptab = (!empty($session_info['session_id'])) ? "tab_".$session_info['session_id'] : "tab_".mt_rand(0,1000000);
$sql = "
CREATE TEMPORARY TABLE ".$temptab." TYPE = HEAP
SELECT image_id, cat_id
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)
ORDER BY RAND()";
$result = $site_db->query($sql);
$sql = "
SELECT DISTINCT t.image_id, t.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".$temptab." AS t
LEFT JOIN ".CATEGORIES_TABLE." AS c ON t.cat_id=c.cat_id
LEFT JOIN ".IMAGES_TABLE." AS i ON t.image_id=i.image_id
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
GROUP BY t.cat_id";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$random_image_cache[$row['cat_id']] = $row;
}
$sql = "
DROP TABLE ".$temptab;
$result = $site_db->query($sql);
}
else {
if (empty($total_images)) {
$sql = "SELECT COUNT(*) as total_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)";
$row = $site_db->query_firstrow($sql);
$total_images = $row['total_images'];
}
if (empty($total_images)) {
return $random_image_cache;
}
$number = ($total_images > 1) ? mt_rand(0, $total_images - 1) : 0;
$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
LIMIT $number, 1";
$random_image_cache[0] = $site_db->query_firstrow($sql);
}
if ($user_info['user_level'] >=ADMIN){
$time2 = getmicrotime();
$timex = $time2 - $time1;
print($timex);
}
return $random_image_cache;
}
c) alter Code mit Zeitmessung
old code for measuring times for admins
function get_random_image_cache() {
global $site_db, $cat_cache, $total_images, $session_info, $user_info;
$random_image_cache = array();
$cat_id_sql = get_auth_cat_sql("auth_viewcat", "NOTIN");
if ($user_info['user_level'] >=ADMIN){
$time1 = getmicrotime();
}
mt_srand((double)microtime() * 1000000);
if (SHOW_RANDOM_CAT_IMAGE) {
$sql = "SELECT DISTINCT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
ORDER BY RAND()";
$result = $site_db->query($sql);
while ($row = $site_db->fetch_array($result)) {
$random_image_cache[$row['cat_id']] = $row;
}
}
else {
if (empty($total_images)) {
$sql = "SELECT COUNT(*) as total_images
FROM ".IMAGES_TABLE."
WHERE image_active = 1 AND cat_id NOT IN ($cat_id_sql)";
$row = $site_db->query_firstrow($sql);
$total_images = $row['total_images'];
}
if (empty($total_images)) {
return $random_image_cache;
}
$number = ($total_images > 1) ? mt_rand(0, $total_images - 1) : 0;
$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name".get_user_table_field(", u.", "user_name")."
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND c.cat_id = i.cat_id
LIMIT $number, 1";
$random_image_cache[0] = $site_db->query_firstrow($sql);
}
if ($user_info['user_level'] >=ADMIN){
$time2 = getmicrotime();
$timex = $time2 - $time1;
print($timex);
}
return $random_image_cache;
}
F? Varianten mit Zeitmessung
b) und
c) muߠnoch folgende Funktion in die functions.php eingef?rden (falls sie nicht schon hier existiert:
For the variants with measuring times
b) and
c) the following function (if not yet exists) must be added to functions.php:
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
Das wars schon...
Wer also mal die Zeit (nur f?se Funktion) messen will, kann das gerne mal tun und seine Ergebnisse hier kund tun.
-----------------------------------------------------------------------------------
Thats alll...
If you measured the times you may publish the result in this thread.
Meine Zeitmessung / my speed comaprison:
Server: AMD XP 1,8 GHz, 256 MB RAM, 120 GB HDD
ca. 12000 Bilder, 205 Kategorien, 2760 registrierte User
alt/old neu/new
1 User
2 User 1,855-1,980 0,585-0,600
3 User
5 User 0,605-0,611
10 User 0,6-1,05
20 User
30 User
bye
effemmess
PS: tomorrow also in english available...
and now the doc is also in english available...