EF Query Performance Issue with MySQL

Question

I am using Entity Framework 6 with MySQL 5.7. I am trying to tune queries and evaluate performance problems. There is a particular query showing up in the Slow Query Log. Here is an entry:

# Time: 2021-02-10T17:28:40.313796Z
# User@Host: user @ localhost [::1]  Id:    89
# Query_time: 40.252085  Lock_time: 0.000044 Rows_sent: 1  Rows_examined: 758678
SET timestamp=1612978120;
SELECT
`Project1`.`Id`, 
`Project1`.`Time`, 
`Project1`.`Message`, 
`Project1`.`Device_Id`, 
`Project1`.`Type_Id`
FROM `DeviceEvents` AS `Project1`
 WHERE (`Project1`.`Device_Id` = 2) AND (`Project1`.`Type_Id` = 7)
 ORDER BY 
`Project1`.`Time` DESC LIMIT 1;

As you can see the query is taking over 40 seconds. If I copy this exact query in Workbench and run it, it takes 0.003 seconds. So, I am not sure what EF is doing that is affecting the execution time when it gets to MySQL. It looks like MySQL is examining a lot of rows even though there is an index on Device_Id, Type_Id, and Time.

Please post the EXPLAIN of your query - both from within EF (using the DbContext.Database object to execute an EXPLAIN) and from within MySQL Workbench. — Dai
– Dai, Commented Feb 10, 2021 at 20:15
The reason for MySQL 5.7 is that this is an older system. Newer systems are using 8 and Core 3.1 and Entity Framework Core. — Psyfun
– Psyfun, Commented Feb 12, 2021 at 12:18

Psyfun · Accepted Answer · 2021-02-10 20:11:16Z

1

After posting this I figured out the problem. Even though there were individual indexes on each of the appropriate columns, there was not a composite index. I added a composite index for (Device_Id, Type_Id, Time) and now the query returns almost immediately.

I am not sure why it would run fast in Workbench without the composite index but not through Entity Framework.

answered Feb 10, 2021 at 20:11

Psyfun

3693 silver badges17 bronze badges

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

3 Comments

Dai Over a year ago

Database engines cache query execution plans - but those plans are keyed by the query text and connection-properties - your Workbench connection may have extra connection-properties set that give enough hints to MySQL to generate a better execution-plan than it can with your EF connection.

Rick James Over a year ago

The query cache may have been turned on -- leading to almost instant query time.

Psyfun Over a year ago

I originally suspected this and changed my test query in Workbench to avoid this scenario, but there may still be techniques built into Workbench that are helping. From now on, I will rely on the real-world performance and not execution plans within Workbench.

Collectives™ on Stack Overflow

EF Query Performance Issue with MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related