On May 6, 2005, at 4:17 PM, Nicholas Leippe wrote:
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
Thanks - but I discovered the problem is really that there is a many- to-one relationship from authors to books, thus it has to do a full table scan because there can be n results for the join.
Thanks!
-- Kimball
-- 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 | `==================================='
.===================================. | This has been a P.L.U.G. mailing. | | Don't Fear the Penguin. | | IRC: #utah at irc.freenode.net | `==================================='
