EXPLAIN EXTENDED

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE TABLE address (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(20),
        address1 VARCHAR(100),
        address2 VARCHAR(100),
        email VARCHAR(255) NOT NULL,
        KEY ix_address_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_filler(20000);
COMMIT;

INSERT
INTO    address (id, name, address1, address2, email)
SELECT  id,
        CASE WHEN RAND(20091117) < 0.1 THEN CONCAT('Name ', id) ELSE NULL END,
        CASE WHEN RAND(20091117 << 1) < 0.1 THEN CONCAT('Address 1 ', id) ELSE NULL END,
        CASE WHEN RAND(20091117 << 2) < 0.1 THEN CONCAT('Address 2 ', id) ELSE NULL END,
        CONCAT('test', ((id - 1) % 20 + 1), '@example.com')
FROM    filler;
&#91;/sourcecode&#93;
</div>

This table has <strong>20,000</strong> records with <strong>20</strong> distinct emails.

To find out the most filled values, we need to do the following:

<ol>
	<li>Select the list of the emails</li>
	<li>For each email, sort the records so that the most filled record goes first. To do this, we just cast the boolean values (like <code>name IS NULL</code>) to the integers and add them up</li>
	<li>Select the first record's id and join on this id</li>
</ol>

This guarantees that for each distinct email in the table, exactly one record will be selected (despite the actual number of records filled).

And here is the query:


SELECT  ai.*
FROM    (
        SELECT  DISTINCT  email
        FROM    address
        ) ad
JOIN    address ai
ON      ai.id =
        (
        SELECT  id
        FROM    address ao
        WHERE   ao.email = ad.email
        ORDER BY
                (name IS NULL) + (address1 IS NULL) + (address2 IS NULL)
        LIMIT 1
        )

Field or reference 'ad.email' of SELECT #3 was resolved in SELECT #1
select `20091117_relevant`.`ai`.`id` AS `id`,`20091117_relevant`.`ai`.`name` AS `name`,`20091117_relevant`.`ai`.`address1` AS `address1`,`20091117_relevant`.`ai`.`address2` AS `address2`,`20091117_relevant`.`ai`.`email` AS `email` from (select distinct `20091117_relevant`.`address`.`email` AS `email` from `20091117_relevant`.`address`) `ad` join `20091117_relevant`.`address` `ai` where (`20091117_relevant`.`ai`.`id` = (select `20091117_relevant`.`ao`.`id` AS `id` from `20091117_relevant`.`address` `ao` where (`20091117_relevant`.`ao`.`email` = `ad`.`email`) order by ((isnull(`20091117_relevant`.`ao`.`name`) + isnull(`20091117_relevant`.`ao`.`address1`)) + isnull(`20091117_relevant`.`ao`.`address2`)) limit 1))

As you can see, not all records are filled completely.

Nevertheless, the query selects exactly one record per email, and this is the most filled record for this email.