PRIMARY key not working

[jeffStrahl]

Hello,
I have a query that is joining multiple tables. I have it optimized rather well, except for one table. I can’t for the life of me figure out why that table is not using the pk as the key. It is causing the query to filesort and use a temp table, changing the query time from 0.003s to 23s, obviously too long.

Attached is a file with the database and query details.

Any assistance is greatly appreciated, for I don’t know what it could be.

[xaprb]

I can’t see what could be causing that, either. Maybe ANALYZE TABLE method_of_payment will help.

[gmouse]

actually I have encountered that old versions of MySQL (5.0.x, have not tried anything more recent) has a hard job selecting the right index for order by. Eg having a multicolumn indices on (a,b) and on (a,c), if you select from tbl where a=1 order by c it would still pick the index on (a,b). Only using force index solves this issue (or adding an arbitrary where-clause like c>0).