Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: 1 2 [3]   Go Down

Author Topic: hotlinking to redirect to the page that contains the image  (Read 55202 times)

0 Members and 1 Guest are viewing this topic.

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: hotlinking to redirect to the page that contains the image
« Reply #40 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.
Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #41 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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: hotlinking to redirect to the page that contains the image
« Reply #42 on: July 23, 2014, 12:27:29 pm »

Done.
Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #43 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).

Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #44 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

Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: hotlinking to redirect to the page that contains the image
« Reply #45 on: July 23, 2014, 01:22:03 pm »

Please try again with valid data.
Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #46 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`)
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: hotlinking to redirect to the page that contains the image
« Reply #47 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.

Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #48 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.
Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #49 on: July 23, 2014, 02:09:50 pm »

Made it.

Code: [Select]
ALTER TABLE cpg15x_pictures DROP INDEX `filename`
Logged

allvip

  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Posts: 1362
Re: hotlinking to redirect to the page that contains the image
« Reply #50 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.
Logged
Pages: 1 2 [3]   Go Up
 

Page created in 0.027 seconds with 20 queries.