Mysql: how to structure this data and search it

I'm new to mysql. Right now, I have this kind of structure in mysql database:

| keyID | Param           | Value
| 123   | Location        | Canada
| 123   | Cost            | 34
| 123   | TransportMethod | Boat
 ...
 ...

I have probably like 20 params with unique values for each Key ID. I want to be able to search in mysql given the 20 params with each of the values and figure out which keyID.

Firstly, how should I even restructure mysql database? Should 开发者_运维百科I have 20 param columns + keyID? Secondly, (relates to first question), how would I do the query to find the keyID?

If your params are identical across different keys (or all params are a subset of some set of params that the objects may have), you should structure the database so that each column is a param, and the row corresponds to one KeyID and the values of its params.

|keyID|Location|Cost|TransportMethod|...|...
|123  |Canada  |34  |Boat ...
|124  | ...
 ...

Then to query for the keyID you would use a SELECT, FROM, and WHERE statement, such as,

SELECT keyID
FROM key_table 
WHERE Location='Canada'
AND Cost=34
AND TransportMethod='Boat'
...

for more info see http://www.w3schools.com/php/php_mysql_where.asp

edit: if your params change across different objects (keyIDs) this will require a different approach I think

The design you show is called Entity-Attribute-Value. It breaks many rules of relational database design, and it's very hard to use with SQL.

In a relational database, you should have a separate column for each attribute type.

CREATE TABLE MyTable (
  keyID           SERIAL PRIMARY KEY,
  Location        VARCHAR(20),
  Cost            NUMERIC(9,2),
  TransportMethod VARCHAR(10)
);

I agree that Nick's answer is probably best, but if you really want to keep your key/value format, you could accomplish what you want with a view (this is in PostgreSQL syntax, because that's what I'm familiar with, but the concept is the same for MySQL):

CREATE OR REPLACE VIEW myview AS
SELECT keyID,
    MAX(CASE WHEN Param = 'Location' THEN Value END) AS Location,
    MAX(CASE WHEN Param = 'Cost' THEN Value END) AS Cost,
    ....
FROM mytable;

Performance here is likely to be dismal, but if your queries are not frequent, it could get the job done.