forum.coppermine-gallery.net

Support => cpg1.5.x Support => cpg1.5 miscellaneous => Topic started by: allvip on September 26, 2013, 03:40:25 pm

Title: hotlinking to redirect to the page that contains the image
Post by: allvip on September 26, 2013, 03:40:25 pm
Hotlinking: when users find your image in Google Images they will be redirect to your gallery, to the page that contains that image. They can not save it directly from google. That brings a lot more traffic (visitors to your gallery) and they will proabibly remember your gallery.
If users keep saving the image directly from google, you will loose a lot of visitors.

Attention: will consume more server resources (CPU time and MySql time) because redirect.php needs to search the database for  the image name and find the id of the page for that image.

A smaller database may help. Small database ( make you database smaller) for less server resource: http://forum.coppermine-gallery.net/index.php/topic,77935.0.html (http://forum.coppermine-gallery.net/index.php/topic,77935.0.html)

found in the forum tutorial do redirect to a image or a link but not to  the page that contains the image

I did this:

Code: [Select]

RewriteEngine on 
RewriteCond %{HTTP_REFERER} !^$ 
RewriteCond %{HTTP_REFERER} !^http(s)?://(www\.)?allvip.us [NC]   
RewriteRule \.(jpg|jpeg|png|gif)$ allvip.us/gallery/displayimage.php [NC,R,L]


what should I write on line 4 of the code to go to displayimage.php of the actual image?
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on September 27, 2013, 09:42:56 pm
As displayimage.php always require a pid parameter, you need to query the database. Please post a link to the tutorial, maybe there's some information that helps us.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on September 28, 2013, 03:03:24 pm
I choosed to use a .htaccess rule from http://www.hongkiat.com/blog/smarter-way-to-prevent-image-hotlinking-with-htaccess/ (http://www.hongkiat.com/blog/smarter-way-to-prevent-image-hotlinking-with-htaccess/)

 now I have this code in my htacees that redirects all goggle images to: http://www.allvip.us/no_hotlinking.html

Code: [Select]
RewriteEngine on 
RewriteCond %{HTTP_REFERER} !^$ 
RewriteCond %{HTTP_REFERER} !^http(s)?://(www\.)?allvip.us [NC]   
RewriteRule \.(jpg|jpeg|png|gif)$ http://www.allvip.us/no_hotlinking.html [NC,R,L]


I would like the to redirect to the page that contains the image.
example when people click  View image and not visit page:

http://www.google.com/imgres?um=1&sa=N&biw=1024&bih=580&hl=en&tbm=isch&tbnid=Q6MCfgnUUesY4M:&imgrefurl=http://allvip.us/gallery/displayimage.php%3Falbum%3D57%26pid%3D10287&docid=uMldMOIWdHBynM&imgurl=http://allvip.us/gallery/albums/userpics/10001/angelina_jolie_000009.jpg&w=682&h=1024&ei=QdBGUoyYI4SPswb7yIF4&zoom=1&ved=1t:3588,r:7,s:0,i:102&iact=rc&page=1&tbnh=222&tbnw=148&start=0&ndsp=11&tx=87&ty=115 (http://www.google.com/imgres?um=1&sa=N&biw=1024&bih=580&hl=en&tbm=isch&tbnid=Q6MCfgnUUesY4M:&imgrefurl=http://allvip.us/gallery/displayimage.php%3Falbum%3D57%26pid%3D10287&docid=uMldMOIWdHBynM&imgurl=http://allvip.us/gallery/albums/userpics/10001/angelina_jolie_000009.jpg&w=682&h=1024&ei=QdBGUoyYI4SPswb7yIF4&zoom=1&ved=1t:3588,r:7,s:0,i:102&iact=rc&page=1&tbnh=222&tbnw=148&start=0&ndsp=11&tx=87&ty=115)

all the images to redirect to the page that has the image
to redirect to http://www.allvip.us/gallery/displayimage.php?album=57&pid=10287 (http://www.allvip.us/gallery/displayimage.php?album=57&pid=10287)

do not know what to look for in the database
the database has exact links of every pictures or albums but I can not write a rule in .htaccess for every image.I have to many pictures.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on September 28, 2013, 03:25:26 pm
www.fansshare.com has this kind of hotlinking:

http://www.google.com/imgres?um=1&sa=N&biw=1024&bih=580&hl=en&tbm=isch&tbnid=dpRuXcqIAk7RDM:&imgrefurl=http://www.fansshare.com/news/eddie-redmayne-thanks-his-big-lips-for-getting-cast-as-angelina-jolie-s-son-in-good-shepherd/&docid=1-fkxIPsfjXP-M&imgurl=http://www.fansshare.com/media/content1/550x298_eddie-redmayne-thanks-his-big-lips-for-getting-cast-as-angelina-jolie-s-son-in--the-good-shepherd--3805.jpg&w=550&h=298&ei=rdZGUu3iAoittAatsYGAAQ&zoom=1&ved=1t:3588,r:2,s:0,i:87&iact=rc&page=1&tbnh=165&tbnw=283&start=0&ndsp=12&tx=165&ty=74 (http://www.google.com/imgres?um=1&sa=N&biw=1024&bih=580&hl=en&tbm=isch&tbnid=dpRuXcqIAk7RDM:&imgrefurl=http://www.fansshare.com/news/eddie-redmayne-thanks-his-big-lips-for-getting-cast-as-angelina-jolie-s-son-in-good-shepherd/&docid=1-fkxIPsfjXP-M&imgurl=http://www.fansshare.com/media/content1/550x298_eddie-redmayne-thanks-his-big-lips-for-getting-cast-as-angelina-jolie-s-son-in--the-good-shepherd--3805.jpg&w=550&h=298&ei=rdZGUu3iAoittAatsYGAAQ&zoom=1&ved=1t:3588,r:2,s:0,i:87&iact=rc&page=1&tbnh=165&tbnw=283&start=0&ndsp=12&tx=165&ty=74)

click view image

and wordpress imaguard plugin  http://wordpress.org/plugins/imaguard/ (http://wordpress.org/plugins/imaguard/)

is a small plugin with a few functions and .htaccess rules but no not understand much
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 01, 2013, 04:31:51 pm
I hadn't looked at the Wordpress plugin, but here's how my idea should work. Somebody tries to directly view your example picture, which is located at
Quote
http://www.allvip.us/gallery/albums/userpics/10001/angelina_jolie_000009.jpg

Now, you need to create an .htaccess rule which passes that information as parameter to a new script instead of your static HTML page, let's call it redirect.php. That script needs some simple code which queries the database to get the picture ID (pid) of the accessed image. It's easy, as Coppermine stores the picture's file path and name in the database (which you just passed to the script). In our example, the file's path would be
Code: [Select]
userpics/10001/and the file's name
Code: [Select]
angelina_jolie_000009.jpgwhich can be extracted with a regular expression or a combination of substr/strpos.

Now, a query like
Code: [Select]
SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE filepath = '{$filepath}' AND filename = '{$filename}'should return the pid and you can redirect the user to the intermediate-sized view. As I've currently no testbed where I can test this, I can just provide you this generic information. If you need code, please let me know and I'll create it as soon as possible.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on October 02, 2013, 09:48:00 am
I did paste your code in phpMyAdmin in the SQL and I got:

Code: [Select]
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['TABLE_PICTURES']} WHERE filepath = '{$filepath}' AND filename = '{$filename}' ' at line 1



yes I do need code
thanks
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 04, 2013, 10:24:50 am
Of course this code won't work, as the variables need to filled with values. I'll add a proper .htaccess rule to my testbed and create working code now.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 04, 2013, 10:52:10 am
Adjust the last line of your .htaccess file to
Code: [Select]
RewriteRule ^(.*\.(jpg|jpeg|png|gif))$ http://www.allvip.us/redirect.php?$1 [NC,R,L]and use this code for redirect.php
Code: [Select]
<?php
define
('IN_COPPERMINE'true);
require(
'include/init.inc.php');

$file $superCage->server->getEscaped('QUERY_STRING');
if (
strpos($file$CONFIG['fullpath']) === FALSE) {
    
header("Location: no_hotlinking.html");
} else {
    
$file str_replace($CONFIG['fullpath'], ''$file);
    
$pos strrpos($file'/') + 1;
    
$filepath substr($file0$pos);
    
$filename substr($file$pos);
    
$pid mysql_result(cpg_db_query("SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE filepath = '{$filepath}' AND filename = '{$filename}' LIMIT 1"), 0);
    if (
$pid) {
        
header("Location: displayimage.php?pid=".$pid);
    } else {
        
header("Location: no_hotlinking.html");
    }
}
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 04, 2013, 04:57:41 pm
Hello André,

I have a question,

Would not it be necessary to consider also $CONFIG['url']?

Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 04, 2013, 06:42:47 pm
That code worked in my testbed, as the query string just returned "albums/.../...".
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 04, 2013, 09:51:22 pm
Hello André,

I replaced "no_hotlinking.html" with an image file and always redirects to the image, never to the intermediate page.

I have verified that everything is correct as you wrote the code.

Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 05, 2013, 01:51:26 pm
Please post an example link to an intermediate-sized image in your gallery.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 05, 2013, 02:39:58 pm
Hello André,

I am now using another hotlinking until I can use yours. I think your hotlinking will be very good.
An example here (http://wonmyo.com/albums/uploads/Logotipos/normal_acdc_logo.png), and I'm using this (http://forum.coppermine-gallery.net/index.php/topic,76414.msg368589.html#msg368589).

Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 07, 2013, 04:20:54 pm
http://wonmyo.com/albums/uploads/Logotipos/thumb_acdc_logo.png shows the image
http://wonmyo.com/albums/uploads/Logotipos/normal_acdc_logo.png shows the image
http://wonmyo.com/albums/uploads/Logotipos/acdc_logo.png redirects to http://wonmyo.com/direct-linking-not-permitted.jpg

Keep in mind that your browser may load images from cache, which will skip the .htaccess rule.

I currently don't know what exactly
I replaced "no_hotlinking.html" with an image file and always redirects to the image, never to the intermediate page.
means. Do you get redirected to your "no_hotlinking.html" image file replacement or do you get the result as above?
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 07, 2013, 04:41:42 pm
Hello André,

In your code I replaced:
Code: [Select]
header ("Location: direct-linking-not-permitted.jpg");instead of
Code: [Select]
header ("Location: no_hotlinking.html");
Always redirects to the image and I hope to be redirected to the intermediate page:
Code: [Select]
header("Location: displayimage.php?pid=".$pid);
Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 07, 2013, 05:09:24 pm
Please try again with this code for redirect.php and post the result:
Code: [Select]
<?php
define
('IN_COPPERMINE'true);
require(
'include/init.inc.php');

echo 
$superCage->server->getEscaped('QUERY_STRING');
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 08, 2013, 01:33:11 am
This new has the same behavior as above, redirects to hotlink image but not to the intermediate image page.

I tried this one too, and nothing:
Code: [Select]
echo $superCage->server->getEscaped('QUERY_STRING', '/^[a-zA-Z0-9&=_\/.-]+$/');


Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 08, 2013, 09:12:25 am
It's not possible that the script redirects you, as it just prints the query string, nothing more. Please post the content of your .htaccess file.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 08, 2013, 09:23:24 am
Hello André,

I did above, is this (http://forum.coppermine-gallery.net/index.php/topic,76414.msg368589.html#msg368589). Anyway I also used htacces content posted above by allvip and with the new rule rewritte said by you to this file redirect.php.

I made sure to try following his instructions.

Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 08, 2013, 09:34:30 am
I still don't know if you adjusted your .htaccess file properly. That's why I asked to post the current content.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 08, 2013, 09:40:25 am
Ahh Ok

For the plugin sef_url

I'm going to uninstall, I'll try without plugin, that I did not try.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 08, 2013, 10:58:50 am
Hello André,

I tested clean, without htaccess and without sef_url plugin.

It seems that does not work on my server. I see no need to disturb more.

With hotlink image is enough for me.

Thank you so much, always.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on October 08, 2013, 11:03:41 am
Of course it won't work without the .htaccess file.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 08, 2013, 07:35:15 pm
Thank you very much Master,

There was a conflict with the plugin sef_url (removed).

Working perfectly 100%, first script you created, here (http://forum.coppermine-gallery.net/index.php/topic,76683.msg370326.html#msg370326)

This htaccess file is ready for redirect.php script, as follows:

Code: [Select]
<Files ".htaccess">
Order Allow,Deny
Deny from All
</Files>

RewriteEngine On

RewriteCond %{REQUEST_FILENAME} -f

RewriteCond %{REQUEST_FILENAME} \.([Bb][Mm][Pp]|[Jj][Pp][Ee]?[Gg]|[Gg][Ii][Ff]|[Pp][Nn][Gg])$ [NC]

<FilesMatch "^(normal_(.*)|thumb_(.*)).([Bb][Mm][Pp]|[Jj][Pp][Ee]?[Gg]|[Gg][Ii][Ff]|[Pp][Nn][Gg])$">
RewriteCond %{HTTP_REFERER} !^$
</FilesMatch>

RewriteCond %{HTTP_REFERER} !^http(s)?://(.*)?example.com [NC]

RewriteCond %{REQUEST_URI} !^/example\.jpg$ [NC]

RewriteRule ^(.*\.([Bb][Mm][Pp]|[Jj][Pp][Ee]?[Gg]|[Gg][Ii][Ff]|[Pp][Nn][Gg]))$ http://example.com/redirect.php?$1 [R,NC,L]

RewriteCond %{REQUEST_FILENAME} -f

RewriteCond %{REQUEST_FILENAME} \.(7z|aac|arj|as(f|x)|avi|bz2|divx|docx?|f4(a|v)|flv|gtar|gz|lzh|m4(a|v)|midi?|mkv|mov(ie)|mp(2|3|4|e?g)|og(g|m)|pdf|qt|ra(m|r)|sw(c|f)|tar|tgz|wm(a|v)|zip)$ [NC]

RewriteCond %{HTTP_REFERER} !^$

RewriteCond %{HTTP_REFERER} !^http(s)?://(.*)?example.com [NC]

RewriteRule ^(.*\.(7z|aac|arj|as(f|x)|avi|bz2|divx|docx?|f4(a|v)|flv|gtar|gz|lzh|m4(a|v)|midi?|mkv|mov(ie)|mp(2|3|4|e?g)|og(g|m)|pdf|qt|ra(m|r)|sw(c|f)|tar|tgz|wm(a|v)|zip))$ http://example.com/redirect.php?$1 [R,NC,L]


Note: To be used in the root folder. Do not forget to replace example.com with your domain and example.jpg your image to redirect.

This other (http://forum.coppermine-gallery.net/index.php/topic,76414.msg368589.html#msg368589) htaccess file does not work with redirect.php, is a different file (free choice, what each one likes).

Regards.
Title: Re: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on October 09, 2013, 01:38:45 am
Adjust the last line of your .htaccess file to
Code: [Select]
RewriteRule ^(.*\.(jpg|jpeg|png|gif))$ http://www.allvip.us/redirect.php?$1 [NC,R,L]and use this code for redirect.php
Code: [Select]
<?php
define
('IN_COPPERMINE'true);
require(
'include/init.inc.php');

$file $superCage->server->getEscaped('QUERY_STRING');
if (
strpos($file$CONFIG['fullpath']) === FALSE) {
    
header("Location: no_hotlinking.html");
} else {
    
$file str_replace($CONFIG['fullpath'], ''$file);
    
$pos strrpos($file'/') + 1;
    
$filepath substr($file0$pos);
    
$filename substr($file$pos);
    
$pid mysql_result(cpg_db_query("SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE filepath = '{$filepath}' AND filename = '{$filename}'"), 0);
    if (
$pid) {
        
header("Location: displayimage.php?pid=".$pid);
    } else {
        
header("Location: no_hotlinking.html");
    }
}


thanks a lot.it worked.
I only edited a little the code for the .htaccess:

Code: [Select]

RewriteEngine on 
RewriteCond %{HTTP_REFERER} !^$ 
RewriteCond %{HTTP_REFERER} !^http(s)?://(www\.)?allvip.us [NC]   
RewriteRule ^(.*\.(jpg|jpeg|png|gif))$ http://www.allvip.us/gallery/redirect.php?$1 [NC,R,L]


because my gallery is not at allvip.us  is in the folder gallery -  allvip.us/gallery
I placed the .htaccess , redirect.php and no_hotlinking.html in the gallery folder.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on October 09, 2013, 03:26:41 am
In created a new .htaccess in the gallery folder only with your code and my original .htaccess with other website rules is still in the public_html
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Niecher on October 09, 2013, 12:45:44 pm
Hello allvip,

Thank you very much for your idea and request, this is a good hotlinking.

Regards.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on October 09, 2013, 09:35:28 pm
even more thanks to andre that took the time to make it
thanks andre for the code is VERY, VERY  important to a website.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on February 13, 2014, 11:34:20 am
If anyone wants all the gallery pages to redirect from example.com to www.example.com (useful for CloudFlare.CloudFlare has no point if users access the site without www.
CoudFlare runs only for www.)

code by Andre Reply #14:

http://forum.coppermine-gallery.net/index.php/topic,77210.0.html (http://forum.coppermine-gallery.net/index.php/topic,77210.0.html)

Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 22, 2014, 06:33:59 pm
Redirect.php is making the CPU time and MySQL time very hight.

Greg told me:

This will result in a full scan of the cpg pictures table for every request because:

    There is no index available that MySQL can use to find the result based on filepath and filename.
    There is no information given to MySQL to allow it to stop after the first (and ONLY) match is found - it will complete the scan...

For the second issue - just adding 'LIMIT 1' to the SQL statement will tell MySQL to stop looking after the first match... We know there can only be one match (the same filepath/filename can only exist once - but MySQL doesn't know that...)

For the first issue - we need to add some kind of index. The perhaps obvious choice would be a 2 column unique index on filepath and filename. Unfortunatly we hit a MySQL limitation of 1000 byte key... Both filepath and filename at defined as varchar(255) - or 510 characters - but when saved in UTF8 - can take up to 3 bytes per character - which exceeds the limit.
We can reduce the amount of data in the index - or take an approach with 1 column index(es).

I would suggest adding the following index (via phpMyAdmin) using your correct cpg table prefix - expecting that in most cases your actual fieldlengths are smaller than 255...:

Code: [Select]
ALTER TABLE `cpg_pictures`  ADD UNIQUE `filepath_filename` (`filepath` (200), `filename` (100))
and changing the SQL statement to:

Code: [Select]
SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE filepath = '{$filepath}' AND filename = '{$filename}' LIMIT 1
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 22, 2014, 06:37:41 pm
In phpMyAdmin I clicked the coppermine database - click SQL button on top - click clear - paste ALTER TABLE ... - click Go.

Error

SQL query:

ALTER TABLE `cpg15x_pictures`  ADD UNIQUE `filepath_filename` (`filepath` (200), `filename` (100))

MySQL said: Documentation
#1061 - Duplicate key name 'filepath_filename'

with:

Code: [Select]
ALTER TABLE `cpg15x_pictures`  ADD INDEX `filename` (`filename`)

Error
SQL query:

ALTER TABLE `cpg15x_pictures`  ADD INDEX `filename` (`filename`)

MySQL said: Documentation
#1061 - Duplicate key name 'filename'

Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 22, 2014, 06:39:47 pm
I can not even tell if the database is optimize.
Is proabibly not a coppermine dev problem,but please help.

How to optimize the database or just the cpg pictures table?
How to make redirect.php take less resources?
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 22, 2014, 06:41:47 pm
I can give my host cpanel password to Greg,Andre or Jeff Bailey.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 10:20:39 am
ALTER TABLE `cpg15x_pictures`  ADD UNIQUE `filepath_filename` (`filepath` (200), `filename` (100))

MySQL said: Documentation
#1061 - Duplicate key name 'filepath_filename'

I assume you try to add the index more than once? If so, that error message is obvious. I just applied both queries on my Coppermine database successfully.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 11:04:14 am
How can I tell if the pictures table is indexed now?
Title: Re: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 11:11:20 am
I assume you try to add the index more than once?

Yes.Twice.
The first time the error was:

Error
SQL query:

ALTER TABLE `cpg15x_pictures`  ADD UNIQUE `filepath_filename` (`filepath` (200), `filename` (100))

MySQL said: Documentation
#1062 - Duplicate entry 'userpics/10001/-barbara_palvin_8.jpg' for key 'filepath_filename'

I deleted -barbara_palvin_8.jpg and tryed again.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 11:19:34 am
How can I tell if the pictures table is indexed now?
Code: [Select]
SHOW INDEX FROM cpg15x_pictures
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 11:30:13 am
Is that ok?
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 12:00:08 pm
As you can see, the index exists.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 12:12:49 pm
Can you please,please help me index all the tables.
Is important for the database to use less server resources.
Is the default coppermine database.Nothing changed.
I belive all the copermine databases look the same.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 12:20:05 pm
Please start a new thread for that topic. Don't forget to add some information what exactly needs to be indexed in your opinion.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 12:24:19 pm
Can you please update reply#7 (redirect.php code) with LIMIT 1.
Is a good think for redirect.php to use less resources.
Use less server resouces is always better even if is a small gallery.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 12:27:29 pm
Done.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 01:04:26 pm
I forgot.
Greg also told me:

Comparison from 'EXPLAIN':
"EXPLAIN SELECT pid FROM `kmn_cpg_pictures` WHERE filepath = 'albums/' AND filename = 'pic.jpg' LIMIT 1"
(the values for filepath and filename don't matter for the EXPLAIN..)

No index:
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra    
1   SIMPLE   cpg_pictures   ALL   NULL   NULL   NULL   NULL   973   Using where

Index:
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra    
1   SIMPLE   cpg_pictures   ref   filename   filename   767   const   1   Using where

Notice the use of a key field (index)  and reduction in number of rows estimated to be examined ... (973 is the number of pics in my test gallery).

Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 01:08:06 pm
I typed:

Code: [Select]
EXPLAIN SELECT pid FROM `cpg15x_pictures` WHERE filepath = 'albums/' AND filename = 'pic.jpg' LIMIT 1

and all I got:

see attachment

Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 01:22:03 pm
Please try again with valid data.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 01:33:26 pm
I think is a disaster.A lot of filename.
Maybe because afer ALTER...`filename` (100)) I used even:

I told Greg I got error with the first one so he told me:

OK... I expected the combination of filepath and filename to be unique - but since I had to use partial columns, they might not be in your case..
A simpler approach - add a non-unique index on filename - will greatly limit the set of rows that filepath has to be checked for...
Use this SQL to add (non-unique) index:

Code: [Select]
ALTER TABLE `cpg15x_pictures`  ADD INDEX `filename` (`filename`)
Title: Re: hotlinking to redirect to the page that contains the image
Post by: Αndré on July 23, 2014, 01:53:08 pm
From your screenshot it seems to work now - fine.

Regarding what Greg said:
we hit a MySQL limitation of 1000 byte key... Both filepath and filename at defined as varchar(255) - or 510 characters - but when saved in UTF8 - can take up to 3 bytes per character - which exceeds the limit.
this probably doesn't apply to your gallery if you don't use single byte characters (e.g. all latin characters, digits). Even if you use multi characters in your file and folder names, it's unlikely (but theoretically possible) that you exceed the limit. As long as you don't use very long file and folder names which contain a lot of (or just) multi byte characters, you could also use the 2 column index. Additionally, as far as I know Coppermine transliterates all non-latin characters in file names during upload, so the only way multi byte characters could occur is in the folder names, which can just be created by the admin. The last sentence apply for cpg1.5.x, so if you upgraded your gallery from cpg1.4.x or earlier, file names may contain multi byte characters.

Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 01:58:35 pm
Yes but now I have possible keys filepath_filename,filename not just filepath_filename because I used the second one too that ADD INDEX `filename`.

Now I have added 2 index: filepath_filename with the first ALTER... and filename with the second.

How do I remove (undo) the second ALTER TABLE:

Code: [Select]
ALTER TABLE `cpg15x_pictures`  ADD INDEX `filename` (`filename`)

I should have just filepath_filename if I use the first ALTER... or just filename if I use the second ALTER...
I made the mistake to use them both.
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 02:09:50 pm
Made it.

Code: [Select]
ALTER TABLE cpg15x_pictures DROP INDEX `filename`
Title: Re: hotlinking to redirect to the page that contains the image
Post by: allvip on July 23, 2014, 02:23:14 pm
Thanks a lot for your help and all your time.I sure asked a lot of questions.
Is not copermine team problem to teach me the index stuff.