does 'KEY' in SHOW CREATE TABLE mean a column is indexed?

Question

I am dealing with a db I did not create. I will be issuing a number of long-running queries involving three integer columns of a large table: id (a primary key), x and y. (I won't be writing/updating records; just issuing queries.)

SHOW CREATE TABLE shows that:

`primary_key` int(11) NOT NULL auto_increment,
`x` int(11) default NULL,
`y` int(11) default NULL,
UNIQUE KEY `id` (`id`),
KEY `my_x` (`x`),
KEY `mh_y` (`y`)

For my purposes, should I create indices on id, x and y? Or do the UNIQUE KEY and KEY lines mean that indices are already present?

thanks!

~l

Answer 1

already present. Use EXPLAIN to see what a query will do (perf wise).

Answer 2

You should create a primary key on primary_key - as it is UNIQUE is not the same as PRIMARY. The other two columns are already indexed - KEY is the same as INDEX in this case.

If you always search by both x and y, having a single key on both of them might improve performance.