LINQ Performance Problem

Question

I have a Joomla site that uses JomSocial. I have a .NET web app that I'm working on that will eventually replace Joomla since I prefer .NET over PHP. Right now I have .NET mobile site that users are using.

LINQ to Entity has made development very speedy, but I'm now in the process of trying to fix performance issues. Sending messages to one another is the #1 activity and there's over 40k messages sent so far. This is also where I have performance issue. Below are the two tables JomSocial uses for storing messages. Below that is my current LINQ code that I'm using, which is returning the results I want, it's just taking two seconds to do it.

I think by the column names you probably can figure out what the data looks like, but if not I can create some and then post that on here in a few as I have to run out for a little bit. I should mention that I'm using the Entity Framework with .NET 3.5 and MySQL w/ the MySQL .NET Connector.

Tables:

delimiter $$
CREATE TABLE `jos_community_msg` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from` int(10) unsigned NOT NULL,
  `parent` int(10) unsigned NOT NULL,
  `deleted` tinyint(3) unsigned DEFAULT '0',
  `from_name` varchar(45) NOT NULL,
  `posted_on` datetime DEFAULT NULL,
  `subject` tinytext NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  KEY `deleted` (`deleted`),
  KEY `from` (`from`)
) ENGINE=MyISAM AUTO_INCREMENT=340 DEFAULT CHARSET=utf8$$

delimiter $$
CREATE TABLE `jos_community_msg_recepient` (
  `msg_id` int(10) unsigned NOT NULL,
  `msg_parent` int(10) unsigned NOT NULL DEFAULT '0',
  `msg_from` int(10) unsigned NOT NULL,
  `to` int(10) unsigned NOT NULL,
  `bcc` tinyint(3) unsigned DEFAULT '0',
  `is_read` tinyint(3) unsigned DEFAULT '0',
  `deleted` tinyint(3) unsigned DEFAULT '0',
  UNIQUE KEY `un` (`msg_id`,`to`),
  KEY `msg_id` (`msg_id`),
  KEY `to` (`to`),
  KEY `idx_isread_to_deleted` (`is_read`,`to`,`deleted`),
  KEY `from` (`msg_from`),
  KEY `parent` (`msg_parent`),
  KEY `deleted` (`deleted`),
  KEY `to_deleted` (`deleted`,`to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$

LINQ:

var messages = (
    from b in context.jos_community_msg
    join i in (
        from i in context.jos_community_msg_recepient
        join a in context.jos_community_msg on i.msg_id equals a.id
        where i.to == userId && && a.deleted == 0
        group a by a.parent into g
        select g.Max(p => p.id)) on b.id equals i
    join a in context.jos_community_msg_recepient on i equals a.msg_id
    orderby b.id descending
    select new MessageHeaderItem()
    {
        IsDeleted = false,
        IsRead = (a.is_read.Value == 0) ? false : true,
        MessageId = b.parent,
        Sent = b.posted_on.Value,
        Subject = b.subject,
        UserId = a.msg_from
    });

    total = messages.Count();
    return messages.Skip(start).Take(max).ToList();

I've tried a bunch of variations, but nothing has made it any quicker. Having the sub select is not good for performance, but I'm not sure how else to get just the last message in the message chain from that table.

Update: Here's the SQL being generated:

SELECT
`Limit1`.`C1`, 
`Limit1`.`C2`, 
`Limit1`.`C3`, 
`Limit1`.`parent`, 
`Limit1`.`posted_on`, 
`Limit1`.`subject`, 
`Limit1`.`msg_from`, 
`Limit1`.`C4`, 
`Limit1`.`C5`, 
`Limit1`.`C6`
FROM (SELECT
`Extent1`.`id`, 
`Extent1`.`parent`, 
`Extent1`.`posted_on`, 
`Extent1`.`subject`, 
`Extent6`.`msg_from`, 
1 AS `C1`, 
cast(0 as decimal(0,0)) AS `C2`, 
CASE WHEN (0 = (`Extent6`.`is_read`)) THEN (cast(0 as decimal(0,0)))  ELSE (cast(1 as decimal(0,0))) END AS `C3`, 
'Test' AS `C4`, 
'' AS `C5`, 
'' AS `C6`
FROM `jos_community_msg` AS `Extent1` INNER JOIN (SELECT
(SELECT
Max(`Extent5`.`id`) AS `A1`
FROM (SELECT
      `jos_community_msg_recepient`.`bcc`, 
      `jos_community_msg_recepient`.`deleted`, 
      `jos_community_msg_recepient`.`is_read`, 
      `jos_community_msg_recepient`.`msg_from`, 
      `jos_community_msg_recepient`.`msg_id`, 
      `jos_community_msg_recepient`.`msg_parent`, 
      `jos_community_msg_recepient`.`to`
      FROM `jos_community_msg_recepient` AS `jos_community_msg_recepient`) AS `Extent4` INNER JOIN `jos_community_msg` AS `Extent5` ON (`Extent4`.`msg_id` = `Extent5`.`id`) OR ((`Extent4`.`msg_id` IS  NULL) AND (`Extent5`.`id` IS  NULL))
 WHERE ((`Extent4`.`to` = 62) AND (0 = (`Extent5`.`deleted`))) AND ((`Extent5`.`parent` = `Project2`.`parent`) OR ((`Extent5`.`parent` IS  NULL) AND (`Project2`.`parent` IS  NULL)))) AS `C1`
FROM (SELECT
62 AS `p__linq__5`, 
`Distinct1`.`parent`
FROM (SELECT DISTINCT 
`Extent3`.`parent`
FROM (SELECT
      `jos_community_msg_recepient`.`bcc`, 
      `jos_community_msg_recepient`.`deleted`, 
      `jos_community_msg_recepient`.`is_read`, 
      `jos_community_msg_recepient`.`msg_from`, 
      `jos_community_msg_recepient`.`msg_id`, 
      `jos_community_msg_recepient`.`msg_parent`, 
      `jos_community_msg_recepient`.`to`
      FROM `jos_community_msg_recepient` AS `jos_community_msg_recepient`) AS `Extent2` INNER JOIN `jos_community_msg` AS `Extent3` ON (`Extent2`.`msg_id` = `Extent3`.`id`) OR ((`Extent2`.`msg_id` IS  NULL) AND (`Extent3`.`id` IS  NULL))
 WHERE (`Extent2`.`to` = 62) AND (0 = (`Extent3`.`deleted`))) AS `Distinct1`) AS `Project2`) AS `Project3` ON (`Extent1`.`id` = `Project3`.`C1`) OR ((`Extent1`.`id` IS  NULL) AND (`Project3`.`C1` IS  NULL)) INNER JOIN (SELECT
      `jos_community_msg_recepient`.`bcc`, 
      `jos_community_msg_recepient`.`deleted`, 
      `jos_community_msg_recepient`.`is_read`, 
      `jos_community_msg_recepient`.`msg_from`, 
      `jos_community_msg_recepient`.`msg_id`, 
      `jos_community_msg_recepient`.`msg_parent`, 
      `jos_community_msg_recepient`.`to`
      FROM `jos_community_msg_recepient` AS `jos_community_msg_recepient`) AS `Extent6` ON (`Project3`.`C1` = `Extent6`.`msg_id`) OR ((`Project3`.`C1` IS  NULL) AND (`Extent6`.`msg_id` IS  NULL))
 ORDER BY 
`id` DESC LIMIT 0,16) AS `Limit1`;

Here's the explain from MySQL:

1   PRIMARY <derived2>  ALL                 16  
    2   DERIVED <derived3>  ALL                 55  Using temporary; Using filesort
    2   DERIVED Extent1 eq_ref  PRIMARY PRIMARY 4   Project3.C1 1   Using where
    2   DERIVED <derived9>  ALL                 333 Using where; Using join buffer
    9   DERIVED jos_community_msg_recepient ALL                 333 
    3   DERIVED <derived6>  ALL                 55  
    6   DERIVED <derived7>  ALL                 55  
    7   DERIVED <derived8>  ALL                 333 Using where; Using temporary
    7   DERIVED Extent3 eq_ref  PRIMARY,deleted PRIMARY 4   Extent2.msg_id  1   Using where; Distinct
    8   DERIVED jos_community_msg_recepient ALL                 333 
    4   DEPENDENT SUBQUERY  Extent5 ref PRIMARY,parent,deleted  parent  4   Project2.parent 2   Using where
    4   DEPENDENT SUBQUERY  <derived5>  ALL                 333 Using where; Using join buffer
    5   DERIVED jos_community_msg_recepient ALL                 333 

Answer 1

Your LINQ query looks pretty good. You use a projection to a DTO (MessageHeaderItem) which allows LINQ to Entities to create a very optimal query. You should however use the SQL profiler to check the actual SQL query that is executed. Perhaps LINQ to Entities fires many queries under the covers. It is also possible that you need some index tuning. Copy the executed query from the SQL profiler to the SQL tuning wizard (part of SQL Management Studio) and see what advice it comes up with.