Search statement improvement

[szymic1]

Hi,
I modify  Coppermina 1.3.3 for my purposes and I've analysed how Coppermine search for picture (using search form).

Usually it is done like that (we looking for pictures "morze kaktus krajobraz"):

[7] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% kaktus %' OR filename LIKE '%kaktus%' OR title LIKE '%kaktus%' OR location LIKE '%kaktus%'OR caption LIKE '%kaktus%'  )

    [8] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% krajobraz %' OR filename LIKE '%krajobraz%' OR title LIKE '%krajobraz%' OR location LIKE '%krajobraz%'OR caption LIKE '%krajobraz%'  )

    [9] => SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ') LIKE '% morze %' OR filename LIKE '%morze%' OR title
LIKE '%morze%' OR location LIKE '%morze%'OR caption LIKE '%morze%'  )

And finaly:
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, title, caption,user1, country_name, pricecat_name FROM cpg133_pictures, cpg133_country, cpg133_pricecat WHERE ( pid in ( LISTO OF ALL PIDS FROM STATEMENTS ABOVE ))

If pictere set is very big final statement can be very very long (I plan to use coppermina to store 10 thousands pictures) and even cause of mySQL or PHP crash.

But it can be done in such way:

SELECT pid FROM cpg133_pictures WHERE ( CONCAT(' ', keywords, ' ')
       LIKE '% morze %' OR filename LIKE '%morze%' OR title LIKE '%morze%' OR location
       LIKE '%morze%'OR caption LIKE '%morze%'  )

AND
( CONCAT(' ', keywords, ' ') LIKE '% kaktus %' OR filename
  LIKE '%kaktus%' OR title LIKE '%kaktus%' OR location
  LIKE '%kaktus%'OR caption LIKE '%kaktus%'  )

AND ( CONCAT(' ', keywords, ' ') LIKE '% krajobraz %'
OR filename LIKE '%krajobraz%' OR title LIKE '%krajobraz%' OR location LIKE
'%krajobraz%'OR caption LIKE '%krajobraz%'  )
       AND user2='CL'

Creation of such dynamic statement is very easy and I do not understand why Coppermina developers use several statements instead one?


P.S I've removed less important parts from statements.