4images Forum & Community
4images Modifications / Modifikationen => Mods & Plugins (Releases & Support) => Topic started by: V@no on June 04, 2005, 12:50:17 AM
-
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... ;)
-
This is a great MOD
I am always search and test 4images to improve performance
And random speed was one of the issues
But I have a question
Is this MOD build-in 1.7.1?
I mean if I am use 1.7.1 than do I need this MOD
Personally I think that Jan should use every optimize MOD here in the new standard 4images
-
open includes/functions.php
and check the function get_random_image_cache() by yourself :wink:
And you'll know if it is included or not 8)
-
Personally I think that Jan should use every optimize MOD here in the new standard 4images
yes, I agree, but the "optimization" should not hurt the compability on different systems.
This mod is a good example, because in order for this mod to work, your mysql account must have "special" privelegies to create/delete temporary tables. On most servers it will work, but on some it wont.
-
Maybe there could be an option to use it or not in the installation.
Ie. a checkbox with text: Use Random image Optimization. And maybe a 'question mark', where it could explain what are the requirements for this optimization to work.
...or maybe the installer could check directly if the optimization can be used, by trying to create&delete a temp tables.
-
wich one is better 2 use a, b, or c if i want to optimize mysql usage .
-
a - is the actualy code, b and c are only for bentchmarks, to see the difference between normal and optimized version ;)
-
thank goodness i found this .
my site was toally screwed up because iof the original lame code ( sorry for being such mean ) but it was going thru hell for th e past 2 months
i simply removed the random image from the template altogether ... having over 30000 images , this was ttoally destroying my site
-
well, as long as your server has lots of memory, this method should work without problem, otherwise you'll get lots of "out of memory" errors.
-
can someone please tell me where where or in what file is this line included ??
[qcode]13197 | username | localhost | dbname | Query | 7471 | Copying to tmp table | SELECT DISTINCT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords |[/qcode]
i tried seeing mysql process stat and it the result displayed almost 95% of these lines / querries . Can i do without it ?
-
never mind ... my fault ... i had earlier edited the rand_image in the templates ... but it simply has a easier solution ... tun of the rand_image and rand_cat_image in the constants file . :lol:
the site is blazing fast now ;) without random image and no more connection maxed out error .
-
I installed this MOD and everything works perfect.
My provider upgraded mysql from Version 4.0.20 to 4.1.18.
And now I get the following error message on every page:
DB Error: Bad SQL Query: CREATE TEMPORARY TABLE tab_885922 TYPE = HEAP SELECT image_id, cat_id FROM 4images_images WHERE image_active = 1 AND cat_id NOT IN (0, 56, 28) ORDER BY RAND()
Access denied for user 'serge2'@'localhost' to database 'photofront'
DB Error: Bad SQL Query: 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, u.user_name FROM tab_885922 AS t LEFT JOIN 4images_categories AS c ON t.cat_id=c.cat_id LEFT JOIN 4images_images AS i ON t.image_id=i.image_id LEFT JOIN 4images_users u ON (u.user_id = i.user_id) GROUP BY t.cat_id
Table 'photofront.tab_885922' doesn't exist
DB Error: Bad SQL Query: DROP TABLE tab_885922
Unknown table 'tab_885922'
With mysqladmin I can create a new table. The provider sais, that I have all rights for creating a new table.
Has anybody installed this MOD and works with MYSQL 4.1.18?
Thanks for helping me!
TIMT
-
There are two different permission options, create tables and create temporary tables.
In phpmyadmin it looks like this:
(http://img221.imageshack.us/img221/9592/fromvno200607100152577jn.png)
-
V@no,
what you think about following alternatives hacks:
1. Many fields in the query are redundant, most users needs only show a thumbnail with image name. So in SELECT statement we can leave only "i.image_id, i.cat_id, i.image_name, i.image_media_file, i.image_thumb_file", also we can remove "LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)". It helped me to significant reduce a query time.
2. Alternative way for ORDER BY RAND is to make selection by image creation time. E.g. select some images with "image_date > d", where d is a random date in (min(image_date), max(image_date)).
I use for my index page this code (it's very hardcoded, it's only as example):
$starttime = time() - 1133463722 - 2419200; //current date - date of permanent first image - month
$randdate = 1133463722 + mt_rand (1, $starttime);
$sql = "SELECT i.image_id, i.cat_id, i.image_name, i.image_media_file, i.image_thumb_file"."
FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)
WHERE i.image_date > ".$randdate." AND i.image_active = 1 AND c.cat_id = i.cat_id AND i.cat_id IN (".get_auth_cat_sql("auth_viewcat").")
ORDER BY i.image_date ASC
LIMIT 1";