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
– Sanosay, Commented Jun 24, 2012 at 19:33
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
– TomTom, Commented Jun 25, 2012 at 4:50
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
– Sanosay, Commented Jun 25, 2012 at 12:12

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

0

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.

answered May 14, 2017 at 22:55

Rick James

144k15 gold badges144 silver badges253 bronze badges

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

Collectives™ on Stack Overflow

Mysql - Entity Framework performance issue on "order by" / Subquery

EDIT :

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

EDIT :

1 Answer 1

Comments

Related