1

why does something like this not work?

mysql> SELECT * FROM employees FORCE INDEX(emp_no) WHERE emp_no = '77899';

where the table looks like this:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

the error i get is this:

ERROR 1176 (42000): Key 'emp_no' doesn't exist in table 'employees'
BigBug
  • 6,202
  • 23
  • 87
  • 138

3 Answers3

3

Just because emp_no is the name of the primary key doesn't mean that it's the name of the index.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX. http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

do this instead

mysql> SELECT * FROM employees FORCE INDEX(PRIMARY) WHERE emp_no = '77899';
Rizwan Kassim
  • 7,931
  • 3
  • 24
  • 34
  • are you sure? this post would suggest otherwise (i think): http://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql – BigBug Oct 06 '13 at 03:11
  • 1
    From the [MySQL manual](http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html) (shortened): _The primary key for a table... has an associated index, for fast query performance._ – jerdiggity Oct 06 '13 at 03:29
  • This was helpful. I still have the issue I think. I do an explain query, and it still claims to be a full table scan. – Urasquirrel Jul 12 '19 at 15:33
1

I think it's complaining (very unhelpfully) because you're doing force index on something that's already an index. Do SHOW INDEXES FROM <<tablename>> to be sure. You shouldn't need the force index clause..

Alkanshel
  • 4,198
  • 1
  • 35
  • 54
1

You can add it to the index list first via this query.

ALTER TABLE employees ADD INDEX emp_no (emp_no);

Jared Dominic Caraan
  • 19
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 05 '22 at 11:14