Can this SQL query be optimized?

Question

This is a query for a Postfix table lookup (smtpd_sender_login_maps) in MariaDB (MySQL). Given an email address it returns the users allowed to use that address. I am using two SQL tables to store accounts and aliases that need to be searched. Postfix requires a single query to return a single result set hence the UNION SELECT. I know there is unionmap:{} in postfix but i do not want to go that route and prefer the union select. The emails.email column is the username that is returned for Postfix SASL authentication. The %s in the query is where Postfix inserts the email address to search for. The reason for matching everything back to the emails.postfixPath is because that is the physical inbox, if two accounts share the same inbox they should both have access to use all the same emails including aliases.

Table: emails
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| email       | varchar(100) | NO   | PRI | NULL    |       |
| postfixPath | varchar(100) | NO   | MUL | NULL    |       |
| password    | varchar(50)  | YES  |     | NULL    |       |
| acceptMail  | tinyint(1)   | NO   |     | 1       |       |
| allowLogin  | tinyint(1)   | NO   |     | 1       |       |
| mgrLogin    | tinyint(1)   | NO   |     | 0       |       |
+-------------+--------------+------+-----+---------+-------+

.

Table: aliases
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| email      | varchar(100) | NO   | PRI | NULL    |       |
| forwardTo  | varchar(100) | NO   |     | NULL    |       |
| acceptMail | tinyint(1)   | NO   |     | 1       |       |
+------------+--------------+------+-----+---------+-------+

.

SELECT email
    FROM emails
    WHERE postfixPath=(
        SELECT postfixPath
            FROM emails
            WHERE email='%s'
            AND acceptMail=1
            LIMIT 1)
    AND password IS NOT NULL
    AND allowLogin=1
UNION SELECT email
    FROM emails
    WHERE postfixPath=(
        SELECT postfixPath
            FROM emails
            WHERE email=(
                SELECT forwardTo 
                    FROM aliases 
                    WHERE email='%s' 
                    AND acceptMail=1)
            LIMIT 1)
    AND password IS NOT NULL
    AND allowLogin=1
    AND acceptMail=1

This query works, it just looks heavy to me and i feel like it should be more streamlined / efficient. Does anyone have a better way to write this or is this as good as it gets?

• I added CREATE INDEX index_postfixPath ON emails (postfixPath) per @The Impaler's suggestion.

@Rick James here is the additional table info:

Table: emails
Create Table: CREATE TABLE `emails` (
`email` varchar(100) NOT NULL,
`postfixPath` varchar(100) NOT NULL,
`password` varchar(50) DEFAULT NULL,
`acceptMail` tinyint(1) NOT NULL DEFAULT 1,
`allowLogin` tinyint(1) NOT NULL DEFAULT 1,
`mgrLogin` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`email`),
KEY `index_postfixPath` (`postfixPath`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table: aliases
Create Table: CREATE TABLE `aliases` (
`email` varchar(100) NOT NULL,
`forwardTo` varchar(100) NOT NULL,
`acceptMail` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

.

Answer 1

The following three indexes will make the query faster:

create index ix1 on emails (allowLogin, postfixPath, acceptMail, password, email);

create index ix2 on emails (email, acceptMail);

create index ix3 on aliases (email, acceptMail);

Answer 2

Part 1:

SELECT  email
    FROM  emails
    WHERE  postfixPath=
    (
        SELECT  postfixPath
            FROM  emails
            WHERE  email='%s'
              AND  acceptMail = 1
            LIMIT  1
    )
      AND  password IS NOT NULL
      AND  allowLogin = 1

With indexes:

emails: (email, acceptMail, password)

I assume acceptMail has only 2 values? The Optimizer cannot know that, so it sees AND acceptMail as a range test. AND acceptMail = 1 fixes that. (No, > 0, != 0, etc, can't be optimized.)

Part 2:

This has 3 layers, and is probably where the inefficiency is.

SELECT e.email
    FROM ( SELECT forwardTo ... ) AS c
    JOIN ( SELECT postfixPath ... ) AS d ON ...
    JOIN emails AS e  ON e.postfixPath = d.postfixPath

This is how the Optimizer might optimize your version. But I am not sure it did, so I changed it to encourage it to do so.

Again, use =1 when testing for "true". Then have these indexes:

aliases:  (email, acceptMail, forwardTo)
emails:   (email, postfixPath)
emails:   (postfixPath, allowLogin, acceptMail, password, email)

Finally, the UNION:

( SELECT ... part 1 ... )
UNION ALL 
( SELECT ... part 2 ... )

I added parentheses to avoid ambiguities about what clauses belong to the Selects versus to the Union.

UNION ALL is faster than UNION (which is UNION DISTINCT), but you might get the same email twice. However, that may be nonsense -- forwarding an email to yourself??

The order of columns in each index is important. (However, some variants are equivalent.)

I think all the indexes I provided are "covering", thereby giving an extra performance boost.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE. "MUL" is especially ambiguous.

(Caveat: I threw this code together rather hastily; it may not be quite correct, but principles should help.)

For further optimization, please do like I did in splitting it into 3 steps. Check the performance of each.