Mysql – hosting images on own database or 3rd party

So you're going to want to store your images on a fast CDN like Cloudfront. Normally I recommend storing pictures in-database, but not 240M of them :)

I'd avoid storing the whole URL, as it's likely to be repetitive. Just store the file name (a five-char case-sensitive name using a-z 0-9 would give you ~900M combinations. If your CDN is case-insensitive wrt URLs then use 6 chars ).

Are there really going to be 240M unique images, or can they be "shared" among entries? Do you expect every user to fill every entry with the max # of images?

You'd want some tables like this:

create table users (
  user_id serial primary key,
  ...
);

create table entries (
  entry_id serial primary key,
  user_id int not null references users(user_id), --index me
  description text,
  ...
);


create table pictures (
  file_name char(5) primary key,
  entry_id int not null references entries(entry_id) --index me
);

or perhaps:

create table pictures (
  picture_id serial primary key,
  file_name char(5) not null unique,
  entry_id int not null references entries(entry_id) -- index me
);

Use connection pooling!

I would prefer to maintain the activity_image table to support more than a single image per activity. Assume, you are storing the actual images in a separate file server and not storing as BLOB in the table itself.

I don't see what performance issues you are facing? Maybe, a bit of elaboration would be better for the sake of clarity.

I would prefer to store the image path along with other image related details in activity_image table itself, mostly the image path value would be constant unless you decide to move away the images to other folder location.

How huge the tables are? How the queries are written? Do you have proper indexing strategy? Is the query columns are hitting the index precisely? What datatypes are used for indexed columns? Is paging concept implemented on your application? At one search how large the resultset is? Are you restricting user's search and load more when the user scroll (assume web/mobile app)?