hotlinking to redirect to the page that contains the image

[Αndré]

Please start a new thread for that topic. Don't forget to add some information what exactly needs to be indexed in your opinion.

[allvip]

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.

[Αndré]

Done.

[allvip]

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).

[allvip]

I typed:

Code Select Expand


EXPLAIN SELECT pid FROM `cpg15x_pictures` WHERE filepath = 'albums/' AND filename = 'pic.jpg' LIMIT 1


and all I got:

see attachment

[Αndré]

Please try again with valid data.

[allvip]

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 Expand


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


[Αndré]

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.

[allvip]

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 Expand


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.

[allvip]

Made it.

Code Select Expand


ALTER TABLE cpg15x_pictures DROP INDEX `filename`


[allvip]

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.