MySQL: What does KEY idx_customers_id_zen(customers_id) do?

[lat9]

I'm in the process of creating a new database table and was looking through the Zen Cart install SQL files for a "good example". I see that many of the tables have not only PRIMARY_KEY definitions, but also KEY definitions like below. What do these extra key definitions do for me? Using the Developers Tool Kit to search for 'idx_customers_id_zen' brings up no results ...

#
# Table structure for table 'files_uploaded'
#

DROP TABLE IF EXISTS files_uploaded;
CREATE TABLE files_uploaded (
  files_uploaded_id int(11) NOT NULL auto_increment,
  sesskey varchar(32) default NULL,
  customers_id int(11) default NULL,
  files_uploaded_name varchar(64) NOT NULL default '',
  PRIMARY KEY  (files_uploaded_id),
  KEY idx_customers_id_zen (customers_id)
) ENGINE=MyISAM COMMENT='Must always have either a sesskey or customers_id';

[swguy]

It's just creating a named index.
http://dev.mysql.com/doc/refman/5.1/...ate-table.html

[lat9]

Thanks, swguy, I'd read through the MySQL docs on CREATE_TABLE ... but it's a bit Greek to me. I took away that it's a database performance thing, and I'm guessing that it's used internally by MySQL. Other than that, I still have no clue why I would/wouldn't include KEY clauses in my newly-minted table.

[swguy]

Think about the design of your table and the searches that will be performed on it. For instance, look at the address_book table. It has a unique key that's automatically generated. But because lookups are done on customers_id, it also creates an index on that field.

[lat9]

Thanks for the update, it's a bit more clear to me now.

[swguy]

Wikipedia covers topics like this well:

http://en.wikipedia.org/wiki/Database_index

http://en.wikipedia.org/wiki/Column_%28database%29

http://en.wikipedia.org/wiki/Relational_database

and so forth.

[DrByte]

PRIMARY KEY is the first and main "index" on the table, and requires (and enforces) uniqueness on the fields listed in its definition (the fields in parentheses)

KEY is a secondary "index" on other fields or combinations of fields. Can be many indexes (KEY declarations), but should be as few as possible.

Optimizing them is based on what SELECT statements need to gain most efficient access to the records, primarily built on WHERE or JOIN clause contents. While an ORDER BY clause does impact, the impact of an ORDER BY is very marginal and should not be a primary reason for ever adding an index to any table.

[dw08gm]

Is there a particular naming convention for the idx_whatever_zen part of the KEY definition?

In KEY idx_customers_id_zen (customers_id), I gather the (customer_id) part refers to the column name, but the reasoning behind idx_customers_id_zen part is not clear to me, as with the following excerpt for the Customers table.

KEY idx_email_address_zen (customers_email_address),
KEY idx_referral_zen (customers_referral(10)),
KEY idx_grp_pricing_zen (customers_group_pricing),
KEY idx_nick_zen (customers_nick),
KEY idx_newsletter_zen (customers_newsletter)

Why the (10) after customers-referral?

Thanks

[DrByte]

1. the idx_ and _zen prefix/suffix are there to denote that those indexes were (most likely) created by Zen Cart initially, as opposed to those added manually by users themselves (such as via phpMyAdmin which just uses the field name itself).

2. the names of the fields indexed are generally included in the name of the index, mainly to add clarity. Some abbreviation is occasionally used where logical.

3. the (10) you mention is an advanced approach to indexing which forces only the first 10 characters of the value to be indexed. This is a requirement for certain field types which can't possibly index the entire field (ie: a longtext field). It shortens the amount of storage required for the indexing, and sometimes even improves speed performance, depending on how the indexes actually get used by the query optimizer engine in mysql