MySQL Bugs: #65723: MySql Provider for EntityFramework produces "bad" SQL for OrderBy

Bug #65723	MySql Provider for EntityFramework produces "bad" SQL for OrderBy
Submitted:	24 Jun 2012 19:31	Modified:	28 Jun 2013 2:41
Reporter:	sanosay sanosay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.5.4.0	OS:	Windows
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any

Description:
I have one table that stores 3k records
When i run this linq query :
repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date).OrderByDescending(foo=>foo.Date).Take(4);

I get this query on mysql side :

SELECT
`Project1`.`Id`, 
`Project1`.`User_id`, 
`Project1`.`Category_id`, 
`Project1`.`Title`, 
`Project1`.`Value`, 
`Project1`.`Keywords`, 
`Project1`.`Description`, 
`Project1`.`Images`, 
`Project1`.`Votes`, 
`Project1`.`Views`, 
`Project1`.`Isvisible`, 
`Project1`.`Isfrontpage`, 
`Project1`.`Istoparticle`, 
`Project1`.`Date`, 
`Project1`.`Expires_date`, 
`Project1`.`Votes_sum`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`User_id`, 
`Extent1`.`Category_id`, 
`Extent1`.`Title`, 
`Extent1`.`Value`, 
`Extent1`.`Keywords`, 
`Extent1`.`Description`, 
`Extent1`.`Images`, 
`Extent1`.`Votes`, 
`Extent1`.`Votes_sum`, 
`Extent1`.`Views`, 
`Extent1`.`Isvisible`, 
`Extent1`.`Isfrontpage`, 
`Extent1`.`Istoparticle`, 
`Extent1`.`Expires_date`, 
`Extent1`.`Date`
FROM `tcms_articles` AS `Extent1`
 WHERE `Extent1`.`Expires_date` >= '2012-06-24 13:41:47.816') AS `Project1`
 ORDER BY 
`Project1`.`Date` DESC LIMIT 4

witch affects performance
Explain of this query :
+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+
| id | select_type | table      | type  | possible_keys | key          | key_len | ref  | rows | Extra          |
+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+
|  1 | PRIMARY     |  | ALL   | NULL          | NULL         | NULL    | NULL | 4054 | Using filesort |
|  2 | DERIVED     | Extent1    | range | expires_date  | expires_date | 8       | NULL | 4053 | Using where    |
+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+
When i query :
SELECT * FROM tcms_articles WHERE expires_date >= '2012-06-24 13:41:47.816' ORDER BY date DESC limit 4
I get 0.01 sec...

Running explain again i get :

+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tcms_articles | index | expires_date  | date | 8       | NULL |   11 | Using where |
+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+

How to repeat:
When i run this linq query :
repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date).OrderByDescending(foo=>foo.Date).Take(4);

I get this query on mysql side :

SELECT
`Project1`.`Id`, 
`Project1`.`User_id`, 
`Project1`.`Category_id`, 
`Project1`.`Title`, 
`Project1`.`Value`, 
`Project1`.`Keywords`, 
`Project1`.`Description`, 
`Project1`.`Images`, 
`Project1`.`Votes`, 
`Project1`.`Views`, 
`Project1`.`Isvisible`, 
`Project1`.`Isfrontpage`, 
`Project1`.`Istoparticle`, 
`Project1`.`Date`, 
`Project1`.`Expires_date`, 
`Project1`.`Votes_sum`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`User_id`, 
`Extent1`.`Category_id`, 
`Extent1`.`Title`, 
`Extent1`.`Value`, 
`Extent1`.`Keywords`, 
`Extent1`.`Description`, 
`Extent1`.`Images`, 
`Extent1`.`Votes`, 
`Extent1`.`Votes_sum`, 
`Extent1`.`Views`, 
`Extent1`.`Isvisible`, 
`Extent1`.`Isfrontpage`, 
`Extent1`.`Istoparticle`, 
`Extent1`.`Expires_date`, 
`Extent1`.`Date`
FROM `tcms_articles` AS `Extent1`
 WHERE `Extent1`.`Expires_date` >= '2012-06-24 13:41:47.816') AS `Project1`
 ORDER BY 
`Project1`.`Date` DESC LIMIT 4

witch affects performance

I have a similar case:

sum = (from k in db.sys_godzinne_serw
               where k.Type == 1 && k.ID_Serwis == ID
               select k.Wartosc).Sum();

produces sql:

SELECT
`GroupBy1`.`A1` AS `C1`
FROM (SELECT
SUM(`Extent1`.`Wartosc`) AS `A1`
FROM (SELECT
      `sys_godzinne_serw`.`Type`,
      `sys_godzinne_serw`.`ID_Serwis`,
      `sys_godzinne_serw`.`Wartosc`,
      `sys_godzinne_serw`.`Brow_Opera`,
      `sys_godzinne_serw`.`Brow_Msie`,
      `sys_godzinne_serw`.`Brow_Firefox`,
      `sys_godzinne_serw`.`Brow_Netscape`,
      `sys_godzinne_serw`.`Brow_Konqueror`,
      `sys_godzinne_serw`.`Lang_PL`,
      `sys_godzinne_serw`.`Lang_EN`,
      `sys_godzinne_serw`.`OS_Windows`,
      `sys_godzinne_serw`.`OS_Linux`,
      `sys_godzinne_serw`.`ChSet_Utf8`,
      `sys_godzinne_serw`.`ChSet_Utf16`,
      `sys_godzinne_serw`.`ChSet_Iso_8859_1`,
      `sys_godzinne_serw`.`CreatedAt`
      FROM `sys_godzinne_serw` AS `sys_godzinne_serw`) AS `Extent1`
 WHERE (1 = `Extent1`.`Type`) AND (`Extent1`.`ID_Serwis` = 741)) AS
`GroupBy1`;

Execution time:  75 seconds (very big table)

manual SQL:
select sum(Wartosc) from sys_godzinne_serw
where type = 1 and id_serwis = 741

Execution time <1 second

This is very critical bug !!!

Fixed as of the upcoming Connector/Net 6.5.7, 6.6.6, and 6.7.4 releases, and here's the changelog entry:

Generated "LINQ to Entities" queries are no longer as nested. In other
words, two similar queries with one nested inside the other are now
flattened into a simple query. This provides better performance for large
result sets.

Thank you for the bug report.

is it solved really because i got Connector/Net 6.9.7 release and it still generates nested SQL query and of course the ‘where’ clause and ‘order by’ clause are on different select statement which makes a huge performance issue in our case for 100k records. Is there any suggestion to get around it?

Regards,
Silvi

The same issue, it's still not fixed in 6.9.8