Where should I place Indexes in MySQL Tables

Question

I have the following three WHERE clauses:

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.

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?

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:

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:

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:

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;

Your code is 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: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Johan, Sep 21 '11 at 22:37

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

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?

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.

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

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.

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?

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.

edited May 23 '17 at 12:20

Community

1
1

answered Sep 21 '11 at 22:51

Johan

74,508
24
191
319

That is one smokin hot answer Johan. You more clearly explained things I have been reading about for days. Thanks a bunch. I'm curious, I have another query in which the WHERE clause is variable, that is to say the contents change depending upon te situation. Would you mind taking a look at it if I posted it in another question? – stefmikhail Sep 21 '11 at 23:14
Yes, but not today, I'm going to bed now. If you put a comment below with a link to the question I will look at it 2morrow. – Johan Sep 21 '11 at 23:50
Ok, so Netherlands... I think that's 8 hours ahead of me ;) So I'll post it before *I* go to bed, and then you should be getting up right about then! Thanks again. – stefmikhail Sep 21 '11 at 23:52
Sorry I didn't get a change to post the other question I wanted you to have a look at, until now. Here it is: http://stackoverflow.com/q/7521010/708274 – stefmikhail Sep 22 '11 at 20:34
I also finally got a chance to thoroughly look at the revised query you provided, and I have some questions. Are you stating that there is no need to use `imgWidth` and `imgHeight` in the `WHERE` of the `secondary_images` part of the query, because it will only ever return 1 row? Just to clarify, `imgId` is the primary key of the `primary_images` table, but if it does not contain both the max values in this case, I don't want it counted. There is a multiple column primary key in the `secondary_images` table: `imgId` which is unique, and `primaryId` which is not. – stefmikhail Sep 22 '11 at 21:35
continuing... `primaryId` is not only part of the primary key of the `secondary_images` table, it is also the foreign key of that table; It references the primary key of the `primary_images` table, which is `imgId`. Looking at your revised query, I understand the need to place the `WHERE` in the inner selects to make use of the indexes; However, I don't think the information I just enumerated is accurately represented in the query. Should there not be full sets of the `WHERE` clause in *both* inner queries? – stefmikhail Sep 22 '11 at 21:42
Yes, if you want 0 rows as an outcome as well then you are 100% right. – Johan Sep 23 '11 at 07:37

score 1 · Answer 2 · answered Sep 21 '11 at 21:37

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.

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.

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).

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

I added an example of the rest of query, as well the composition of both tables. I am heading out, but I will try to answer any questions, and comment on your answer as soon as possible. Thank you so much for the response. :) — stefmikhail, Sep 21 '11 at 22:09

Where should I place Indexes in MySQL Tables

2 Answers2