On Friday 06 May 2005 04:03 pm, Kimball Larsen wrote: > Though this is not strictly linux related, I have run into something > funny with a MySQL query that even Google has not been able to shed > light on... > > 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.
While AUTHOR_ID may be a field, and may be indexed, FORCE KEY(x) is expecting x to be the name of the index--not the name of the column. In this case, the name of the index is 'PRIMARY'. Try: explain select CONCAT(FIRST_NAME,' ', LAST_NAME) as AUTHORS from AUTHOR FORCE KEY(PRIMARY), AUTHORED au where au.TITLE_ID = 1001 and AUTHOR.AUTHOR_ID = au.AUTHOR_ID -- Respectfully, Nicholas Leippe Sales Team Automation, LLC 1335 West 1650 North, Suite C Springville, UT 84663 +1 801.853.4090 http://www.salesteamautomation.com .===================================. | This has been a P.L.U.G. mailing. | | Don't Fear the Penguin. | | IRC: #utah at irc.freenode.net | `==================================='
