Concern over Photo Meta Data
By Josh Lewis on Wednesday, 28 May 2014
Posted in General Issues
Likes 0
Views 696
Votes 0
I'm a little concerned about the database layout for the photo meta data located in _social_photos_meta. For two photos it stores quite a few records as seen here:



Eventually if I have a million photos posted, 16 million records would need to be created for those photos. I'm concerned that the number of records might slow down the system when the database is called upon.

Joomla stores a set of data in a single record for users in the following format (in the table _users):

{"admin_style":"","admin_language":"","language":"","editor":"codemirror","helpsite":"","timezone":"America\/Los_Angeles"}

In fact, they not only have the set but other data in a single record as seen here:



I'm not sure which way is the right way, but I'm raising this as a concern to make sure the best practices are being used. Would a dataset slow it down vs the current method being used?
Good catch Josh I'm surprised as to why Mark's team did not use JSON format to store Photo Metadata. Let's see their reason
LAC Webadmin
·
Wednesday, 28 May 2014 12:14
·
0 Likes
·
0 Votes
·
0 Comments
·
We're storing data this way so we could perform advanced photo search in the future, e.g.
- Search for photos taken by a specific camera model
- Search for photos taken within a certain ISO range
or generate statistics like of how many users uses iPhone to upload photos etc.

Storing in a JSON string has been considered but due to limitations of MySQL, searching within JSON string using SQL query isn't quite possible, well, most servers aren't running MySQL >= 5.7.
Jensen Tonne
·
Wednesday, 28 May 2014 12:17
·
0 Likes
·
0 Votes
·
0 Comments
·
Thanks for the explanation Jensen. If data sets create a restriction for advanced searches, why not have each piece of info in it's respective column? For example in a single record have the first column be the id, the second being the photo id, path, camera type, camera settings, ect. It would be a long row, but would save on a lot of records.

Because ES has the best uploading system I have ever seen in Joomla (I've tried out many of the best ones out there), I don't have a choice but to use the one build in ES. As a result I have to make sure the best practices are being used.
Josh Lewis
·
Wednesday, 28 May 2014 15:01
·
0 Likes
·
0 Votes
·
0 Comments
·
subscribed
n00bster
·
Wednesday, 28 May 2014 15:17
·
0 Likes
·
0 Votes
·
0 Comments
·
Interesting. Subscribed.
Antonio Starace
·
Friday, 30 May 2014 17:43
·
0 Likes
·
0 Votes
·
0 Comments
·
Subscribed++
Kevin
·
Saturday, 31 May 2014 06:39
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Jensen,

Got it! I haven't thought of search data

Are you guys thinking of converting ES tables to InnoDB in the future? I know you guys are thinking of customers that uses shared hosting but with ES features you guys are adding I don't see it becoming a Social Platform that will thrive in shared hosting which has limited resources. With that in mind, I think you will limit and force yourself in creating a code that will run on a limited resources when you're supposed to design code that will make ES run efficiently. For example, you can take advantage of InnoDB foreign relationship between records which I guess now in MyISAM it is done via php or javascript code, and other processes that you can let MySQL use its horsepower by using maybe stored procedure instead of using Php doing the query and process the result. Data Integrity is important too

Joomla 3.x is going that route since almost or all of the tables are now InnoDB.
LAC Webadmin
·
Saturday, 31 May 2014 10:16
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post