Hello all,
using mysqlversion 2.23.30
I have the problem that mysql doesn't want to use an index.
I tried USE INDEX() but that doesn't help either.
I have a tables with 250k rows
person(
id int, (indexed)
name varchar(40),
fatherid int,
motherid int,
creationtime timestamp (indexed)
)
I want to see the last 10 persons and their parents that are added to my database.
SELECT p.name, father.name as fathername, mother.name as mothername
FROM person as p
LEFT JOIN person as father ON (p.fatherid=father.id)
LEFT JOIN person as mother ON (p.motherid=mother.id)
ORDER BY p.creationtime DESC LIMIT 10;
I thought that mysql would use the INDEX on creationtime to easely
find the 10 rows and than use the other indexes to fetch their parents.
But mysql preferes to scan the whole table (250K rows) and than to sort the result.
This is of course very very slow.
How can I force mysql to use the index on creationtime?
Or is this a mysql bug?
Because mysql uses the right INDEX for little querys like this
"SELECT id FROM person ORDER BY creationtime LIMIT 10;"
I use this query as a workaround and fetch the persons in a little loop.
But thats not the best solution a thing :-/
best regards,
Gunnar von Boehn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
