mysql is scanning table despite index-CodePudding

I have the following mysql query that I think should be faster. The database table has 1 million records and the query table 3.5 seconds

set @numberofdayssinceexpiration = 1; 
set @today  = DATE(now());
set @start_position =  (@pagenumber-1)* @pagesize;

SELECT *
FROM   (SELECT ad.id,
               title,
               description,
               startson,
               expireson,
               ad.appuserid UserId,
               user.email   UserName,
               ExpiredCount.totalcount
        FROM   advertisement ad
               LEFT JOIN (SELECT servicetypeid,
                                  Count(*) AS TotalCount
                           FROM   advertisement
                           WHERE  Datediff(@today,expireson) =
                                  @numberofdayssinceexpiration
                                  AND sendreminderafterexpiration = 1
                           GROUP  BY servicetypeid) AS ExpiredCount
                       ON ExpiredCount.servicetypeid = ad.servicetypeid
               LEFT JOIN aspnetusers user
                       ON user.id = ad.appuserid
        WHERE  Datediff(@today,expireson) = @numberofdayssinceexpiration
               AND sendreminderafterexpiration = 1
        ORDER  BY ad.id) AS expiredAds
LIMIT 20 offset 1;

Here's the execution plan:

Here are the indexes defined on the table:

I wonder what I am doing wrong.

Thanks for any help

CodePudding user response：

First, I would like to point out some problems. Then I will get into your Question.

LIMIT 20 OFFSET 1 gives you 20 rows starting with the second row.
The lack of an ORDER BY in the outer query may lead to an unpredictable ordering. In particular, the Limit and Offset can pick whatever they want. New versions will actually throw away the ORDER BY in the subquery.
DATEDIFF, being a function, makes that part of the WHERE not 'sargeable'. That is it can't use an INDEX. The usual way (which is sargeable) to compare dates is (assuming expireson is of datatype DATE):
```
  WHERE expireson >= CURDATE() - INTERVAL 1 DAY
```
Please qualify each column name. With that, I may be able to advise on optimal indexes.
Please provide SHOW CREATE TABLE so that we can see what column(s) are in each index.

CodePudding user response：

WHERE  Datediff()=...

mysql can't know where to start.Then he will scan the whole table to try every possibility