[RESOLVED] 'PRIMARY KEY' and 'KEY'. What is the difference?

Paul_help_

Hi guys I just have a simple question for you.

I am aware of what the 'PRIMARY KEY' is for, but I struggle to understand what it means when a table column (in MySql) is defined as 'KEY':

KEY(name);

What is the difference and how is a plain old KEY definition different from a PRIMARY KEY definition?

Paul.

NogDog

KEY and INDEX are synonyms in MySQL. PRIMARY KEY is a subset of KEY/INDEX, being the designated UNIQUE KEY/INDEX to be used to identify a given row in the table.

Paul_help_

So if I define a column as a KEY, does it make any difference at all?

Derokorian

Paul help!;11057831 wrote:
So if I define a column as a KEY, does it make any difference at all?

It will make joins and where clauses using the column possibly perform faster.

Paul_help_

I was just wondering.

Say I had a table with the fields named 'id', 'forename' and 'surname'.

'id' is the primary key and has an auto increment.

'forename' is declared as having an INDEX.

... and 'surname' is just a regular old column.

What I wanted to know was, if I specified a SELECT statement which just collected the 'forename' values, would the result set come back as an alphabetically ordered list (since this key has been assigned an index)?

... or does it not work this way?

Paul.

laserlight

If you want it to be ordered, then use ORDER BY, in which case the index would be used for efficient sorting.

Paul_help_

But, what I don't understand is, if the index has already sorted/ordered the column, why is ORDER BY even needed?

I don't get it.

here is an extract from a book I am reading, to show you where my confusions come from:

Remember that ORDER BY works faster on a column that has an index, because indexes are already
sorted in order.

cluelessPHP

Say for example you have a table populated by orders, you may wish to count how many of each product is sold starting from either most sold or least sold product, so in example you would use ORDER BY

Derokorian

Paul help!;11057839 wrote:
But, what I don't understand is, if the index has already sorted/ordered the column, why is ORDER BY even needed?

I don't get it.

here is an extract from a book I am reading, to show you where my confusions come from:

the index doesn't do any ordering, its a side storage to make using that field in some way faster. You might want to read up on http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

NogDog

Assigning an index to a column (or group of columns) tells the DBMS to do additional magic stuff[SUP]TM[/SUP] in the way it stores the data in order to optimize searches against it, as well as sorts on it. The reason we don't generally just put an index on every column is that the magic stuff[SUP]TM[/SUP] comes with a cost: some amount of additional processing for each submit/update on that data. It does not do anything (that us normal users can depend on, anyway) with regard to the order the rows are stored in the table.