Here is the query:
explain select CONCAT(FIRST_NAME,' ', LAST_NAME) as AUTHORS from AUTHOR, AUTHORED au where au.TITLE_ID = 1001 and AUTHOR.AUTHOR_ID = au.AUTHOR_ID
which yields this:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE AUTHOR ALL PRIMARY null null null 87434
1 SIMPLE au eq_ref PRIMARY PRIMARY 16 btb.AUTHOR.AUTHOR_ID,const 1 Using where; Using index
For anyone not familiar with the explain command, this basically lets you know how the MySQL engine will run your query. In this case, it says that it will do a full table scan on AUTHOR, even though it found a PRIMARY key on author (AUTHOR_ID is defined as a primary key).
So, I then said this:
explain select CONCAT(FIRST_NAME,' ', LAST_NAME) as AUTHORS from AUTHOR FORCE KEY(AUTHOR_ID), AUTHORED au where au.TITLE_ID = 1001 and AUTHOR.AUTHOR_ID = au.AUTHOR_ID
Which says this:
Key column 'AUTHOR_ID' doesn't exist in table
Now, I'm stumped. AUTHOR_ID definitely exists in the AUTHOR table, and it is definitely a key. Further, I have tried to reference it via the full path of DBNAME.TABLE.COL, but it still says it does not exist.
Anyway, if anyone can lend some insight I'm all ears.
-- Kimball
.===================================. | This has been a P.L.U.G. mailing. | | Don't Fear the Penguin. | | IRC: #utah at irc.freenode.net | `==================================='
