scale down tables _exif and _hit_stats

[sinti]

Hi there,

is there a way to scale down the two very big tables _exif and _hit_stats?

In my gallery I now have about 6000 pictures. And the procedure of making a backup of the database is getting harder and harder. My backup *.sql-file has now a size of about 45MB!

I'm thinking of something like deleting the exif-entries I'm not displaying anyway. After my fancy there is too much stored in this exif-table. Does anybody have an idea?

Greez
Sinti

[Joachim Müller]

If you're not using EXIF, then turn it off (Config: Read EXIF data in JPEG files). If you're not using hit_stats, turn it off as well (Config: Keep detailed hit statistics). The documentation already says that turning those options on has an impact on performance and database size.

[sinti]

I know that. I know how to turn off.

I'm using EXIF! Mainly date, lens aperture, hight, width, shutter time, model and some other. But not the other stuff!

I'm really searching a way to scale down these tables, thats why i'm asking! Any other hints?

[Nibbler]

Compress the backup.

[nobody-xxl]

TRUNCATE your EXIF-Table every week, if you kept the original uploaded files.

I don't understand why EXIF-Data can't be read out of the file, every time the file is displayed. Writing it to the Database doesn't make any sence for me.

Regards,
nobody

[Nibbler]

Reading it from the file every time it is viewed is not efficient.

[nobody-xxl]

Reading it from the file every time it is viewed is not efficient.

That's maybe true for a big gallery with many visitors, but for some smaller galleries (with many files and less visitors) there should be the posibility to stop writing in exif-table, since not everybody has unlimited database space.
In my opinion shifting from "using database space" to "using processor time" should be possible as an option for exif usage.

[Nibbler]

It's easy enough to change the code if it's that important to you.

include/exif_php.inc.php

Code Select Expand


        //Check if we have the data of the said file in the table
        $sql = "SELECT * FROM {$CONFIG['TABLE_EXIF']} ".
                  "WHERE filename='".addslashes($filename)."'";

        $result = cpg_db_query($sql);

        if (mysql_num_rows($result) > 0){
                $row = mysql_fetch_array($result);
                mysql_free_result($result);
                $exifRawData = unserialize($row["exifData"]);
        } else {
          // No data in the table - read it from the image file
          $exifRawData = read_exif_data_raw($filename,0);
          // Insert it into table for future reference
          $sql = "INSERT INTO {$CONFIG['TABLE_EXIF']} ".
                    "VALUES ('".addslashes($filename)."', '".addslashes(serialize($exifRawData))."')";
          $result = cpg_db_query($sql);
        }


becomes simply

Code Select Expand

$exifRawData = read_exif_data_raw($filename,0);

[nobody-xxl]

Thank you very much Nibbler.

... I love Coppermine!

Regards,
nobody