mySQL complicated conditional join queries for efficiency over multiple queries and PHP

Using your database structure, this is what I came up with:

   SELECT ui.name, id.added, id.info, emails.email,
          CASE WHEN ev.id IS NULL THEN 'NO' ELSE 'YES' END AS email_verified
     FROM user_items AS ui
     JOIN item_data AS id ON id.item = ui.name
     JOIN (
            SELECT item, MAX(added) AS added
            FROM item_data
            GROUP BY item
        ) AS id_b ON id_b.item = id.item AND id_b.added = id.added
     JOIN emails ON emails.added = id.added
LEFT JOIN emails_verified AS ev ON ev.email = emails.email

But as others have pointed out, the database is poorly designed. This query will not perform well on a table with a lot of data, since there are no aggregate functions for this purpose. I understand that in certain situations you have little to no control over database design, but if you want to actually create the best situation, you should be emphatic to whomever can control it that it can be improved.

One of the biggest optimizations that could be made is to add the current item_data ID to the user_items table. That way the subquery to pull that wouldn't be necessary (since right now we're essentially joining item_data twice).

Converting this to CI's query builder is kind of a pain in the ass because of the sub query. Assuming you're only working with MySQL DBs, just stick with $this->db->query().

Added from your edit:

This query returns one email per row, it does not group them together. I left the CONCAT stuff out because it's one more thing that slows down your query -- your PHP can put the emails together afterwards much faster.

Yes, the subquery is that part -- a query within a query (pretty self-explanatory name :wink:). I wouldn't call it creating a temporary table, because that's something you can actually do. More like retrieving a subset of the information in the table, and using it kind of like a WHERE clause. The subquery is what finds the most recent row in your item_data table, since we have to figure it out ourselves (again, proper database design would eliminate this).

When we say you can optimize your database design, it doesn't mean you can't have it set up in a similar way. You made it sound like the DB could not be altered at all. You have the right idea as far as the overall scheme, you're just implementing it poorly.

Database Design

Here's how I would lay this out. Note that without knowing the whole extent of your project, this may need modification. May also not be 100% the best optimized on the planet -- I'm open for suggestions for improvement. Your mileage may vary.

User Items

CREATE TABLE `users_items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Defines the relationship between a base item and a user.

Items

CREATE TABLE `items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(50) NOT NULL DEFAULT '',
  `created_on` datetime NOT NULL,
  `current_version` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Your items table should have all of your items' base information -- things that will not change on a per-revision basis. Notice the current_version column -- this is where you'll store the ID from the versions table, indicating which is most recent (so we don't have to figure it out ourselves).

Items Versions (history)

CREATE TABLE `items_versions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `added` datetime NOT NULL,
  `info` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is where you'd store the history of an item -- each update would create a new row here. Note that the item_id column is what ties this row to a particular base item.

Emails

CREATE TABLE `emails` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL DEFAULT '',
  `verified` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Since emails can be shared between multiple products, we'll end up using what's called a many-to-many relationship. Emails can be tied to multiple products, and a product can be tied to multiple emails. Here we defined our emails, and include a verified column for whether it has been verified or not.

Item Emails

CREATE TABLE `items_versions_emails` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `version_id` int(11) NOT NULL,
  `email_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Assuming the emails are tied to an item version and not the base item, this is the structure you want. Unfortunately, if you have a ton of versions and never change the email(s), this will result in a lot of repeated data. So there's room for optimization here. If you tie emails to the base item, you'll have less repeated data, but you'll lose the history. So there's options for this. But the goal is to show how to set up DB relationships, not be 100% perfect.

That should give you a good start on how to better lay out your DB structure.

Another Update

Regarding speed, inserting a new item version and then updating the related item row with the new version ID will give you much better performance than requiring a subquery to pull the latest update. You'll notice in the solution for your original structure, the item_info table is being joined twice -- once to join the most recent rows, and again to grab the rest of the data from that recent row (because of the way GROUP BY works, we can't get it in a single join). If we have the recent version ID stored already, we don't need the first join at all, which will improve your speed dramatically (along with proper indexing, but that's another lesson).

I wouldn't recommend ditching the base items table, but that's really up to you and your application's needs. Without a base item, there's no real way to track the history of that particular item. There's nothing in the versions that shows a common ancestor/history, assuming you're removing the item_id column.