Mysql - Entity Framework performance issue on “order by” / Subquery

Question

I have one table that stores 3k records,

I'm new with Entity Framework and ORMs but i can understand that something going wrong.

When i run this linq query :

repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date
                            && foo.Istoparticle==true
                            && foo.Isfrontpage==false)
                        .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

It takes about 3.50 sec to exequte this query.

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 |
+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+

I don't understand why this is happening.

Entity Framework 4.3 MySQL Connector Net 6.5.4.0

EDIT :

The tcms_articles :


CREATE TABLE `tcms_articles` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `User_id` int(10) unsigned DEFAULT NULL,
  `Category_id` int(10) unsigned DEFAULT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `Value` longtext,
  `Keywords` varchar(255) NOT NULL DEFAULT '',
  `Description` varchar(255) NOT NULL DEFAULT '',
  `Images` longtext NOT NULL,
  `Votes` int(10) unsigned NOT NULL DEFAULT '1',
  `Votes_sum` int(10) unsigned NOT NULL DEFAULT '5',
  `Views` int(10) unsigned NOT NULL DEFAULT '0',
  `Isvisible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `Isfrontpage` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Istoparticle` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `Expires_date` datetime NOT NULL DEFAULT '2099-12-31 00:00:00',
  `Date` datetime NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `article_users` (`User_id`) USING BTREE,
  KEY `article_section` (`Category_id`) USING BTREE,
  KEY `Isvisible_index2` (`Isvisible`) USING BTREE,
  KEY `Istoparticle_index2` (`Istoparticle`) USING BTREE,
  KEY `Expires_date_index2` (`Expires_date`) USING BTREE,
  KEY `isfrontpage2` (`Isfrontpage`) USING BTREE,
  KEY `Date_index2` (`Date`) USING BTREE,
  CONSTRAINT `tcms_articles_ibfk_1` FOREIGN KEY (`Category_id`) REFERENCES `tcms_categories` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tcms_articles_ibfk_2` FOREIGN KEY (`User_id`) REFERENCES `tcms_users` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8;

So why Linq produces this query and how/can i fix this?

As i see the problem is the subquery. Is there any alternative way to write it or it is a problem of the provider (MySql Connector)? — Sanosay
Anyone can see why this stupid Subquery is in there for a start? Looking at the LINQ statement I see no reason for it to start with. — TomTom
I think that it is for projection reasons because of the anonymous type (foo) but LINQ expects that the Server (MsSql, Mysql etc) to optimize the query. But this is not happening in MySql :/ — Sanosay

Rick James Rick James · Accepted Answer · 2017-05-14T22:55:21

repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date  -- Note 1
                        && foo.Istoparticle==true
                        && foo.Isfrontpage==false)
                    .OrderByDescending(foo=>foo.Date)       -- Note 2
                    .Take(4);

Use foo.Expires_date in both places.

Mysql - Entity Framework performance issue on “order by” / Subquery

EDIT :

1 Answers