EXIF cache very large

[Sim]

After migrating 280,000 photos into XFMG for our ZooChat migration last week, I rebuilt the EXIF data and was a bit alarmed to find that the xengallery_media table was now very large.

Before I rebuilt the data, the table was around 60MB for 280,000 photos, but now it's 1.9GB according to phpMyAdmin, or 1.4GB when I add it up myself using sum(length(<column>))

media_exif_data_cache_full is 1.2GB
media_exif_data_cache is 160GB



Is there a more efficient way of storing this data? At least move it to a different table perhaps?

I'm a little concerned that storing the data directly in the main media table will place an unnecessary strain on the database server, since it will be trying to cache all of the EXIF data as well as the other data, when it won't actually need the EXIF data until the media is actually displayed.

Can I suggest some optimisation be performed for future versions?

 

[Chris D]

It's something in mind for 2.0.

For now, you could just clear those two columns and not rebuild the data.

 

[Sim]

Yeah, except my users really really want to see that EXIF data

Our users are very passionate about their photos - indeed I have a very long list of requests for improvements to the gallery.

I'm going to have to do some work on it to keep them happy while I wait for v2 to become available

 

[Chris D]

I think if you just clear the media_exif_data_cache_full column, that should be fine. The data from the media_exif_data_cache column is what is displayed.

UPDATE xengallery_media
SET media_exif_data_cache_full = ''

Whatever size is left after clearing that column is basically the best you're going to get it, but I suspect it should be a few hundred MB smaller, at least.

 

[Sim]

Cool.

1.9GB down to 312MB for that table is much more reasonable.

Overall DB usage down from 3.2GB to 1.7GB

Thanks.