我应该在哪里放置索引在MySQL表中

I have the following three WHERE clauses:

我有以下三个WHERE子句:

WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight

WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight

WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight"

They are acting upon two MySQL InnoDB tables joined in the query with UNION ALL.

它们使用UNION ALL在查询中加入两个MySQL InnoDB表。

I am not sure how I should set up the Indexes in these two tables; Whether I should have a Multi-Column Index, with imgWidth and imgHeight, or if it should include primaryId as well?

我不确定如何在这两个表中设置索引;我是否应该有一个多列索引,imgWidth和imgHeight,或者它是否应该包含primaryId?

Is it true that a query can only use one index? If not, could I set up each column as an index?

查询只能使用一个索引是真的吗?如果没有,我可以将每列设置为索引吗?

Or would a Multi-Column Index not work in this case?

或者在这种情况下多列索引不起作用?

Here is an example of the entire query for the first WHERE clause. The others are the same, with the respective clauses:

以下是第一个WHERE子句的整个查询的示例。其他是相同的,各自的条款:

SELECT 'allEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight

Here is the schema of the primary_images table:

这是primary_images表的模式:

CREATE  TABLE IF NOT EXISTS `new_arrivals_images`.`primary_images` (
  `imgId` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `imgTitle` VARCHAR(255) NULL DEFAULT NULL ,
  `view` VARCHAR(45) NULL DEFAULT NULL ,
  `secondary` ENUM('true','false') NOT NULL DEFAULT false ,
  `imgURL` VARCHAR(255) NULL DEFAULT NULL ,
  `imgWidth` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgHeight` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgDate` DATETIME NULL DEFAULT NULL ,
  `imgClass` ENUM('Jeans','T-Shirts','Shoes','Dress Shirts','Trackwear & Sweatwear') NULL DEFAULT NULL ,
  `imgFamily` ENUM('Hugo Boss','Lacoste','True Religion','7 For All Mankind','Robin\'s Jeans','Robert Graham') NULL DEFAULT NULL ,
  `imgGender` ENUM('Men\'s','Women\'s') NOT NULL DEFAULT Mens ,
  PRIMARY KEY (`imgId`) ,
  UNIQUE INDEX `imgDate_UNIQUE` (`imgDate` DESC) )
ENGINE = InnoDB;

And the schema for the secondary_images table:

以及secondary_images表的架构:

CREATE  TABLE IF NOT EXISTS `new_arrivals_images`.`secondary_images` (
  `imgId` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `primaryId` SMALLINT(6) UNSIGNED NOT NULL ,
  `view` VARCHAR(45) NULL DEFAULT NULL ,
  `imgURL` VARCHAR(255) NULL DEFAULT NULL ,
  `imgWidth` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgHeight` SMALLINT(6) UNSIGNED NULL DEFAULT NULL ,
  `imgDate` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`imgId`, `primaryId`) ,
  INDEX `fk_secondary_images_primary_images` (`primaryId` ASC) ,
  UNIQUE INDEX `imgDate_UNIQUE` (`imgDate` DESC) ,
  CONSTRAINT `fk_secondary_images_primary_images`
    FOREIGN KEY (`primaryId` )
    REFERENCES `new_arrivals_images`.`primary_images` (`imgId` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

2 个解决方案

#1

Is it true that a query can only use one index?

查询只能使用一个索引是真的吗?

No. That would be silly.

不,那太傻了。

If not, could I set up each column as an index?

如果没有,我可以将每列设置为索引吗?

Yes that's an option, but only if you use the column independently of each other.
If you always combine the fields, like it seems you do here, it's more efficient to use a compound index.

是的,这是一个选项,但仅当您使用列彼此独立时。如果你总是把字段组合在一起,就好像你在这里做的那样,使用复合索引会更有效。

I am not sure how I should set up the Indexes in these two tables; Whether I should have a Multi-Column Index, with imgWidth and imgHeight, or if it should include primaryId as well?

我不确定如何在这两个表中设置索引;我是否应该有一个多列索引,imgWidth和imgHeight,或者它是否应该包含primaryId?

If you want to can use a compound index combining (imgWidth, imgHeight)
You must remember though that you cannot access the index on imgHeight without also using imgWidth in the where clause.
You must always use the left-most part (or all) of a compound index.

如果你想使用复合索引组合(imgWidth,imgHeight)你必须记住,你不能在imgHeight*问索引而不在where子句中使用imgWidth。您必须始终使用复合索引的最左侧部分(或全部)。

On InnoDB the primary key is always included in every secondary index, so it is counterproductive to include that.

在InnoDB上,主键始终包含在每个二级索引中,因此包含它会适得其反。

Added bonus on InnoDB
If you only select indexed fields, InnoDB will never actually read the tabledata, because all the data needed is in the index. This will speed up things a lot.

在InnoDB上添加了奖励如果您只选择索引字段,InnoDB将永远不会实际读取表数据,因为所需的所有数据都在索引中。这将加速很多事情。

You have an SQL-injection hole
Your code seems to have an SQL-injection hole. Please surround all your $vars in single quotes: where field1 = '$var' ... and don't forget to use $var = mysql_real_escape_string($var); before injecting them into the query. See: How does the SQL injection from the "Bobby Tables" XKCD comic work?

你有一个SQL注入漏洞你的代码似乎有一个SQL注入漏洞。请用单引号括住所有$ vars:where field1 ='$ var'...并且不要忘记使用$ var = mysql_real_escape_string($ var);在将它们注入查询之前。请参阅:“Bobby Tables”XKCD漫画中的SQL注入如何工作?

For speed and safety the query should read:

为了速度和安全性,查询应为:

SELECT 'allEqual' AS COL1, COUNT(*) AS imgCount FROM (
    SELECT imgId AS primaryId FROM primary_images pi
    WHERE pi.ImgId = '$imgId' 
      AND pi.imgWidth = '$maxImageWidth' 
      AND pi.imgHeight = '$maxImageHeight'
    UNION ALL 
    SELECT primaryId FROM secondary_images si
    WHERE si.primaryId = '$imgId' 
      AND si.imgWidth = '$maxImageWidth'    
      AND si.imgHeight = '$maxImageHeight'  
) AS union_table

This way the proper indexes will be used and no unneeded data is retrieved.
MySQL cannot use an index on the unioned data because it's a merge of two different tables. That's why you need to do the where in the inner selects.

这样就可以使用正确的索引,也不会检索到不需要的数据。 MySQL不能在联合数据上使用索引,因为它是两个不同表的合并。这就是为什么你需要做内在选择的地方。

#2

Does your primaryId column have any duplicates? Or is it a primary key? If it's a primary key, then it will also serve as a fine index. In InnoDB, it probably already is an index if it's a primary key.

您的primaryId列是否有任何重复项?或者它是主键?如果它是主键,那么它也将作为一个很好的索引。在InnoDB中,如果它是主键,它可能已经是一个索引。

Put another way, how discriminating is your WHERE clause primaryId = $imgId ? If it typically matches none, or exactly one, or just a few rows, then another index won't help much. If it matches hundreds or thousands of rows, another index may well help.

换句话说,WHERE子句primaryId = $ imgId有多么区别?如果它通常匹配none,或者只匹配一行,或者只是几行,那么另一个索引将无济于事。如果它匹配数百或数千行,则另一个索引可能会有所帮助。

Queries can definitely use multiple indexes.

查询肯定可以使用多个索引。

This is one of those cases where the big question is "what are you trying to do?" It seems like you're trying to select an image where either or both dimensions match your input.

这是一个大问题是“你想做什么?”的案例之一。您似乎正在尝试选择其中一个或两个尺寸与您的输入相匹配的图像。

Consider making it more efficient by redoing the logic and getting rid of your UNION ALL clause (which turns into three queries).

考虑通过重做逻辑并摆脱UNION ALL子句(变成三个查询)来提高效率。

    WHERE primaryId = $imgId 
      AND (imgWidth = $maxImageWidth OR imgHeight = $maxImageHeight)

#1