Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Jul 28 '11 at 19:57

This question came from our site for professional system and network administrators.

    
Insufficient information: What kind of searching are we doing (searching for a single row where all attributes match? Searching partial attributes?), how much data are we talking (tens of rows? tens of thousands? tens of millions?), etc. –  voretaq7 Jul 28 '11 at 19:50
    
Sorry about that, I'll try to be more concise. In the first case, the row must match all 17 columns, in the second case, it must match all the numbers exactly. There will be approx 1000 rows. (I'd be interested in knowing the answer for ~100,000 rows as well as I might work with larger amounts of data in the future). –  Vadoff Jul 28 '11 at 20:05
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
I see! Thanks a lot, this helped clarify a lot for me. –  Vadoff Jul 28 '11 at 20:31
    
I have another question as well if you don't mind. Let's say there's 100 items with the 17 digit value that I want, out of a list of 100,000. I'm assuming finding the first value would take log2(100,000), but would finding the next 99 that match only take 99+1 steps? –  Vadoff Jul 28 '11 at 20:44
    
That question depends on the particulars of how indexes are implemented in the DB's storage engine -- I'm inclined to say yes, but I'm not familiar enough with MySQL's storage engines and indexing to say for sure. –  voretaq7 Jul 28 '11 at 20:47
    
Problem what I see is when u have onecolumn is, Lets say you have values coming in between 1 to 99 , then you want to add the leading 0 infront. But if your column is int then it will not save the 0 infront. In that case at some time , there occurs same numbers . –  Hari K T Aug 1 '11 at 17:32
add comment

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 !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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