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

.

Sorry i don't understand, are you saying it needs to be optimized, or you saying it is already optimized? — Private_Citizen
– Private_Citizen, Commented Mar 3, 2021 at 17:31
No not plain text, they are hashed, i left size (50) instead of sizing it to exact hash size in case i change the hash type in the future. — Private_Citizen
– Private_Citizen, Commented Mar 3, 2021 at 18:17

Rick James · Accepted Answer · 2021-03-04 00:13:19Z

1

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.

answered Mar 4, 2021 at 0:13

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Private_Citizen Over a year ago

I added the table info you suggested in the above original post.

Private_Citizen Over a year ago

Yes, i also don't like the 3 layers in the 2nd select. My intent in posting this was to see if someone could figure out an entirely different way to structure this query, like maybe a creative table join using only one select to get the same result. Im not married to the current syntax.

Private_Citizen Over a year ago

And yes, Im using all of the tiny-int's as booleans and only setting true/false (1/0).

Rick James Over a year ago

@Private_Citizen - You have 2 tables, each with PRIMARY KEY(email). It would probably be better to simply combine the tables. Is there an argument for keeping them separate?

Private_Citizen Over a year ago

No technical argument, just the logical separation of these are accounts and these are aliases, which could go offsite and not even to an account. Plus it would be ugly IMO having an account row inserted just to put alias=this.email. I would have to add two more columns to the emails table. And not sure yet if that would simplify the query or not because i would still have to return results from two columns (email or forwardTo). Your thoughts?

|

The Impaler · Accepted Answer · 2021-03-04 02:09:05Z

0

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);

edited Mar 4, 2021 at 2:09

answered Mar 3, 2021 at 17:32

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

3 Comments

Private_Citizen Over a year ago

I see what you saying. Wouldn't it be better to only index postfixPath then reverse order looking for postfixPath first then check bool conditions vs adding all those indexes. I will edit the query showing this.

The Impaler Over a year ago

@Private_Citizen It depends if you want the engine to use the index for access purposes only, or for filtering and covering purposes as well. Your option will provide you some level of performance improvement, but the indexes I included are close to optimal. Nevertheless they are more expensive. I would suggest to try both options and measure them.

Rick James Over a year ago

ix1 starts with a "range", (IS NOT NULL) so the rest is not useful. Move it to later in the index.

Collectives™ on Stack Overflow

Can this SQL query be optimized?

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related