What would be the best way to model my simple table?

Question

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?

Or, searching for the right row through a table with only 1 column with those 17 numbers concatenated together as a single bigint?

The numbers when combined are all unique from one another (no row has the same values/value as any other). I'm new to indexes and what not, so I have no idea how I should model my table/server logic.

Answer 1

If we're talking about something where you may want to query on one or more of the attributes my suggestion would be multiple columns, indexed appropriately (for a small, infrequently-changing data set, index all the columns).
This works best for the case where you may want to run queries like
SELECT * FROM table WHERE attribute1=1 AND attribute3=2
since you will have several indexed columns and searches on one or more of them will be relatively efficient.

If we're talking about something where you only ever want to query on the aggregate (all attributes match) a single column will be more efficient in terms of storage, and likely in terms of indexing as well.
This works best for cases where the previous example would always devolve into
SELECT * FROM table WHERE attribute1=1 AND … AND attribute17=17
as rather than having to cross-check across multiple columns it will simply be
SELECT * FROM table WHERE attributeColumn=1234567 -- Rather than having to scan 17 indexes (or examine the contents of 17 columns in every row) you will just have the single index scan.

Answer 2

IMHO the second option sounds more viable but you may still want to know individually what each column position has for a value.

Here is a sample table

CREATE TABLE searchtable
(
    attr01 TINYINT DEFAULT 0,
    attr02 TINYINT DEFAULT 0,
    attr03 TINYINT DEFAULT 0,
    attr04 TINYINT DEFAULT 0,
    attr05 TINYINT DEFAULT 0,
    attr06 TINYINT DEFAULT 0,
    attr07 TINYINT DEFAULT 0,
    attr08 TINYINT DEFAULT 0,
    attr09 TINYINT DEFAULT 0,
    attr10 TINYINT DEFAULT 0,
    attr11 TINYINT DEFAULT 0,
    attr12 TINYINT DEFAULT 0,
    attr13 TINYINT DEFAULT 0,
    attr14 TINYINT DEFAULT 0,
    attr15 TINYINT DEFAULT 0,
    attr16 TINYINT DEFAULT 0,
    attr17 TINYINT DEFAULT 0,
    searchkey CHAR(17) NOT NULL,
    PRIMARY KEY (searchkey)
) ENGINE=MyISAM;

You will be inserting the 17 integers like this:

INSERT INTO searchtable
(attr01,attr02,attr03,attr04,attr05,
attr06,attr07,attr08,attr09,attr10,
attr11,attr12,attr13,attr14,attr15,
attr16,attr17) VALUES
(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4,1);

You will need a trigger to create the searchkey for these 17 columns

DELIMITER $$

CREATE TRIGGER searchtable_ai AFTER INSERT ON searchtable FOR EACH ROW
BEGIN
    SET NEW.searchkey = 
        CONCAT(
            attr01,attr02,attr03,attr04,attr05,
            attr06,attr07,attr08,attr09,attr10,
            attr11,attr12,attr13,attr14,attr15,
            attr16,attr17);
END; $$

DELIMITER ;

In order to see if a set of 17 columns exist you will have to concatenate them and then query the table like this:

SELECT COUNT(1) FOUNDKEY FROM searchtable WHERE searchkey = '1234123412341';

That way, you get either 0 or 1 as the answer as to whether the 17-value combination you are seeking exists. The reason all 17 columns are manifested is to allow for the individual querying of those specific columns, should you need it. If you do not need it then change the table to this:

CREATE TABLE searchtable
(
    searchkey CHAR(17) NOT NULL,
    PRIMARY KEY (searchkey)
) ENGINE=MyISAM;

and change the insert to this

INSERT INTO searchtable VALUES (CONCAT(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4,1));

and ditch the trigger.

I also recommend using MyISAM over InnoDB in this case in order to alleviate additional table overhead.

Give it a Try !!!