Working with EF4 and MySql

Need some advice working with EF4 and MySql.

I have a table with lots of data items. Each item belongs to a module and a zone. The data item also has a timestamp (ticks). The most common usage is for the app to query for data after a specified time for a module and a zone. The data should be sorted.

Problem is that the query selects to many rows and the database server will be low on memory resulting in a very slow query. I tried to limit the query to 100 items but the generated sql will only apply the limit after all the items has been selected and sorted.

dataRepository.GetData().WithModuleId(ModuleId).InZone(ZoneId).After(ztime).OrderBy(p

=> p.Timestamp).Take(100).ToList();

Generated SQL by the MySql .Net Connector 6.3.6

 SELECT  
`Project1`.`Id`,  
`Project1`.`Data`,  
`Project1`.`Timestamp`,  
`Project1`.`ModuleId`,  
`Project1`.`ZoneId`,  
`Project1`.`Version`,  
`Project1`.`Type`  
FROM (SELECT  
`Extent1`.`Id`,  
`Extent1`.`Data`,  
`Extent1`.`Timestamp`,  
`Extent1`.`ModuleId`,  
`Extent1`.`ZoneId`,  
`Extent1`.`Version`,  
`Extent1`.`Type`  
FROM `DataItems` AS `Extent1`  
 WHERE ((`Extent1`.`ModuleId` = 1) AND (`Extent1`.`ZoneId` = 1)) AND  
(`Extent1`.`Timestamp` > 634376753657189002)) AS `Project1`  
 ORDER BY  
`Timestamp` ASC LIMIT 100  

Table definition

CREATE TABLE  `mydb`.`DataItems` (

  `Id`开发者_JAVA百科 bigint(20) NOT NULL AUTO_INCREMENT,  
  `Data` mediumblob NOT NULL,  
  `Timestamp` bigint(20) NOT NULL,  
  `ModuleId` bigint(20) NOT NULL,  
  `ZoneId` bigint(20) NOT NULL,  
  `Version` int(11) NOT NULL,  
  `Type` varchar(1000) NOT NULL,  
  PRIMARY KEY (`Id`),  
  KEY `IX_FK_ModuleDataItem` (`ModuleId`),  
  KEY `IX_FK_ZoneDataItem` (`ZoneId`),  
  KEY `Index_4` (`Timestamp`),  
  KEY `Index_5` (`ModuleId`,`ZoneId`),  
  CONSTRAINT `FK_ModuleDataItem` FOREIGN KEY (`ModuleId`) REFERENCES  
`Modules` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,  
  CONSTRAINT `FK_ZoneDataItem` FOREIGN KEY (`ZoneId`) REFERENCES `Zones`  
(`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION  
) ENGINE=InnoDB AUTO_INCREMENT=22904 DEFAULT CHARSET=utf8;  

All suggestions on how to solve this are welcome.

What's your GetData() method doing? I'd bet it's executing a query on the entire table. And that's why your Take(100) at the end isn't doing anything.

I solved this by using the Table Splitting method described here: Table splitting in entity framework