Working with Keys (PRIMARY, UNIQUE, and KEY)

[Xeoncross]

Well, I have been moving along in my understanding of DB's, and this brings me to a new subject I can't find on google - KEY. What is KEY for? For example, look at drupal's 'node' table to better understand my question.

 

CREATE TABLE `node` (
  `nid` int(10) unsigned NOT NULL auto_increment,
  `vid` int(10) unsigned NOT NULL default '0',
  `type` varchar(32) NOT NULL default '',
  `title` varchar(128) NOT NULL default '',
  `uid` int(10) NOT NULL default '0',
  `status` int(4) NOT NULL default '1',
  `created` int(11) NOT NULL default '0',
  `changed` int(11) NOT NULL default '0',
  `comment` int(2) NOT NULL default '0',
  `promote` int(2) NOT NULL default '0',
  `moderate` int(2) NOT NULL default '0',
  `sticky` int(2) NOT NULL default '0',
  PRIMARY KEY  (`nid`,`vid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_type` (`type`(4)),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `status` (`status`),
  KEY `uid` (`uid`),
  KEY `node_moderate` (`moderate`),
  KEY `node_promote_status` (`promote`,`status`),
  KEY `node_created` (`created`),
  KEY `node_changed` (`changed`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `nid` (`nid`)
) TYPE=MyISAM;

 

I know what PRIMARY and UNIQUE keys are for - but what is "KEY" for? Is it another way to reference a column? Or does it do more? And where can I find examples/information on it?

 

[Flayra]

KEY is used to index tables. Usually you index tables for 2-3 columns you often search for (ie: firstname, lastname and e-mail) at the same time. Otherwise it is simply a method to speed up table searching (SELECT queries) but more keys will slow down action queries (INSERT, DELETE, UPDATE queries) since every query needs to manipulate the index files as well. As a consequence, more indexes will also add to the size of the database.

[Xeoncross]

So then what would this mean?

 

  KEY `node_title_type` (`title`,`type`(4)),

 

Index the title and type field up to four chars?

[fenway]

Title, full, then first 4 chars of type.

[Xeoncross]

Thanks a lot. 

It seems that this is not a very common topic.

 

So if I had the following KEY index:

  KEY `node_status_type` (`status`,`type`,`nid`),

Then I could use this to query the DB for something simple without all the overhead searching right?

That way I would have something like:

SELECT * FROM table WHERE `node_status_type` = '1page4505'
//Instead of 
SELECT * FROM table WHERE (`nid` = '4505' AND type = 'page' AND status = 1)

Right?

 

[fenway]

That's right... but if you search for type without status, it won't be indexed.

 

edit: didn't actually read the previous post

[Barand]

That way I would have something like:

Code:

 

SELECT * FROM table WHERE `node_status_type` = '1page4505'

//Instead of

SELECT * FROM table WHERE (`nid` = '4505' AND type = 'page' AND status = 1)

 

Right?

 

That's right...

 

What ! ??? ???

[fenway]

That way I would have something like:

Code:

 

SELECT * FROM table WHERE `node_status_type` = '1page4505'

//Instead of

SELECT * FROM table WHERE (`nid` = '4505' AND type = 'page' AND status = 1)

 

Right?

 

That's right...

 

What ! ??? ???

I don't know what I was smoking when I replied... `node_status_type` isn't even a column, so that question doesnt' really make any sense.  While the multi-column index would represent all three columns, it's not a simple concaternation... and even if it was, you still couldn't reference it that way.  Moreover, why would you want to!?!?

 

Thanks for setting me straight, Barand.  I thought the first select was of the first of the columns, and read "or" rather than "instead".  My bad.

[Xeoncross]

Ok, I'm lost now 

 

So can someone give me an example of the right way to use keys?

[fenway]

Keys are "used" automatically... the question is which columns to index.

[Xeoncross]

Keys are "used" automatically... the question is which columns to index.

 

Ok, so which columns to index? 

 

I still don't have a real-working example of what a key is all about.

[fenway]

Read though this refman page and see if that clears anything up.