0

I have the following table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `state` TINYINT(4) NOT NULL DEFAULT '0',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`)
);

And I have the following query:

SELECT 1
FROM table2 
JOIN table1 ON table1.sha256 = table2.sha256
WHERE table1.is_ignored = FALSE AND table1.state = 1

table2 has 20m rows indexed by sha256.

table1 has 5m rows.

I am considering how to index table1 (not many inserts).

  1. Will creating an index of sha256_state_is_ignored index increase performance for this query comparing to 3 separate indexes on each the columns?(sha256 index is very dominant here)

  2. Will it change if I make the combined index as state_sha256_is_ignored instead of sha256_state_is_ignored.(thats because sha256 already has an index from his unique index)

EDIT:

This is my final table:

CREATE TABLE IF NOT EXISTS maint.files_state_global (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `source` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-default(cloud), 1-dynamic compsInOrg and vt',
    `state` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-unknown, 1 - white, 2- black',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    `date_in` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`),
    INDEX `source` (`source`),
    INDEX `is_ignored` (`is_ignored`),
    INDEX `last_seen` (`last_seen`),
    INDEX `state_is_ignored_sha256` (`state`, `is_ignored`, `sha256`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
  • I must have a single index on source/last_seen/sha256.
  • sha256 is unique.
  • state and is_ignored must have index and will always come together(90% of the cases with sha256 as well).

So other then changing the sha256 to PK and removing id, are my indexes optimized?

1
WHERE table1.is_ignored = FALSE AND table1.state = 1

says to start a composite index with those two columns (in either order). Then, looking at the rest of the SELECT, I see only sha256 being mentioned. So, you may as make a "covering" index:

INDEX(is_ignored, state, sha256);

Meanwhile, get rid of id, assuming it is not really used elsewhere. This will

  1. shrink the disk footprint, thereby helping performance,
  2. get rid of a unique index, thereby helping insert performance.

This leads to

PRIMARY KEY(sha256)

Meanwhile, table2 needs an index (possibly the PK) starting with sha256.

But... Digests (such as sha256) and UUIDs are terrible for performance due to their randomness. Once the size of those two tables becomes more than your innodb_buffer_pool_size, performance will go downhill. SSDs will help some. More RAM will help a lot. but you won't get much past 1K queries/sec.

More on building indexes.

  • remove id?? isnt every table should have running id as PK? – omri Apr 30 '17 at 12:36
  • A PK is necessary, but an AUTO_INCREMENT is not. I went on to suggest replacing id with sha256 as the PK. Perhaps you are using JOINing via id? – Rick James Apr 30 '17 at 15:05
  • no, id is only for auto_inc – omri May 1 '17 at 7:01
  • I updated my table to my actual table, other then the sha256 to PK, are the indexes optimized for your opinion? – omri May 1 '17 at 7:13
  • Don't bother indexing flags (is_ignored); such indexes are unlikely to be used. An index is optimized for a query; without seeing the rest of the queries, I cannot further answer your question. For example, if no query uses source in WHERE, then that index is unnecessary. If, on the other hand, all such queries also filter on is_ignored, then the index could be composite. Etc. – Rick James May 1 '17 at 16:31
0

My view is that sha256_state_is_ignored index will improve performance as this index will become a covering index and the a second lookup on large table to fetch values for state and is_ignored won't be required.

Having state as a prefix won't be as beneficial due to low cardinality.

  • But... Without state and is_ignored first in the index, the entire index must be scanned to do the filtering! "Cardinality" is a very tenacious myth. – Rick James Apr 30 '17 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.