C# Entity FrameWork MySQL Slow Queries Count()
- by Matthew M.
Hello,
I'm having a serious issue with MySQL and Entity Framework 4.0. I have dropped a Table onto the EF Designer surface, and everything seems OK. However, when I perform a query in the following fashion:
using(entityContext dc = new entityContext()) {
int numRows = dc.myTable.Count();
}
The query that is generated looks something like this:
SELECT `GroupBy1`.`A1` AS `C1`
FROM (SELECT Count(1) AS `A1`
FROM (SELECT `pricing table`.`a`,
`pricing table`.`b`,
`pricing table`.`c`,
`pricing table`.`d`,
`pricing table`.`e`,
`pricing table`.`f`,
`pricing table`.`g`,
`pricing table`.`h`,
`pricing table`.`i`
FROM `pricing table` AS `pricing table`) AS `Extent1`) AS `GroupBy1`
As should be evident, this is an excruciatingly unoptimized query. It is selecting every single row! This is not optimal, nor is it even possible for me to use MySQL + EF at this point.
I have tried both the MySQL 6.3.1 [that was fun to install] and DevArt's dotConnect for MySQL and both produce the same results. This table has 1.5 million records.. and takes 6-11s to execute!
What am I doing wrong ? Is there any way to optimize this [and other queries] to produce sane code like:
SELECT COUNT(*) FROM table
?
Generating the same query using SQLServer takes virtually no time and produces sane code.
Help!
Thanks!
Matthew
