I have two identical mysql databases running on the same server. The old one works fine. The new one is getting terrible performance with 'order by'

I've been in the middle of migrating a drupal-5 site to drupal-6. Unfortunately, once I made a copy of the drupal-5 database, the new db has terrible performance problems. Queries like this one:


SELECT DISTINCT(n.nid), n.title, n.created FROM node n INNER JOIN term_node tn ON n.nid = tn.nid WHERE n.type <> 'acidfree' AND tn.tid = 402 AND n.status = 1 ORDER BY n.nid, limit 1;


can take several seconds to execute on the new db while the identical query completes in under a millisecond on the old db. I've run mysqlcheck --repair and --optimize, with no benefit, and the hosting provider is insisting that it's my lousy sql that's causing the problem.

Any suggestions on how to fix this? Queries like that one are used to load random photos onto pages of the site and some pages are taking as long as a minute to load.

EDIT: After 2 weeks of complaints about this, the problem suddenly resolved itself (within minutes of posting here). The provider insists they didn't change anything.....

I might as well close this question for now. Thanks for the suggestions.

---