GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

PRIMARY key not working

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • PRIMARY key not working

    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.

  • #2
    I can't see what could be causing that, either. Maybe ANALYZE TABLE method_of_payment will help.

    Comment


    • #3
      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).

      Comment

      Working...
      X