使用日期排序优化mysql连接查询

Question

简化数据库/表结构我有两个表的情况，我们存储'items‘和item属性(两者之间的关系是1-N)

我正在尝试优化下面的查询，该查询将获取hotdeals部分中的最新商品。为此，我们有一个item_property表，它存储项目部分以及许多其他项目元数据

注意:不能通过更改表结构来优化查询，例如:我们不能简单地将部分作为列添加到item表中，因为我们可以为每个项目提供无限数量的部分。

下面是两个表的结构：

CREATE TABLE `item` (
  `iditem` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `itemname` varchar(200) NOT NULL,
  `desc` text NOT NULL,
  `ok` int(11) NOT NULL DEFAULT '10',
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`iditem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `item_property` (
  `iditem` int(11) unsigned NOT NULL,
  `proptype` varchar(64) NOT NULL DEFAULT '',
  `propvalue` varchar(200) NOT NULL DEFAULT '',
  KEY `iditem` (`iditem`,`proptype`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

下面是查询：

SELECT *
FROM item 
JOIN item_property ON item.iditem=item_property.iditem 
WHERE 
    item.ok > 70 
    AND item_property.proptype='section'
    AND item_property.propvalue = 'hotdeals' 

ORDER BY item.date_created desc
LIMIT 20

哪种索引是优化此查询的最佳索引？现在优化器(Explain)将使用temporary和filesort，处理大量的行(连接的大小)

目前，表和表都是MyIsam，但如果确实需要优化查询，可以将其更改为InnoDB

谢谢

Answer 1

item_property.idOption和item_property.type列的类型是什么？如果它们包含的选项数量有限-将它们设置为ENUM (如果它们还没有)。枚举值将自动编入索引。当然，您还应该对item_property.iditem和item.date_created列进行索引。这将增加表的大小，但将大大加快按这些字段连接和排序的查询速度。

Answer 2

关于数据正确性的注意事项：

NOT NULL的最大好处之一是防止程序创建没有正确指定所有列的行。有一个缺省值会让它变得无用。

有一个空白的proptype或propvalue是可以的吗？这些字段中的空格是什么意思？如果不设置proptype是可以的，那么删除NOT NULL约束。如果必须始终设置proptype，那么使用DEFAULT ''将不会使您避免插入到行中但忘记设置proptype的情况。

在大多数情况下，您希望在列上使用NOT NULL或DEFAULT 'something'之一，但不能同时使用两者。