This should not be slow.

May 6, 2010 10:13 AM

MySQL issue: Even though my desktop should blow my server away hardware wise, it's struggling for some unknown reason on a set of similar queries suddenly, where it didn't before. Details inside:

Here's the query, 3 tables, taking over 7 seconds to run locally, 47ms on server.

SELECT annualSalesRangeValue, count(*) as numCompanies
FROM companies c, annual_sales_ranges asr, company_industries ci
WHERE c.annualSalesRangeId = asr.annualSalesRangeId
AND c.companyId = ci.companyId
AND ci.industryId = 1594
GROUP BY annualSalesRangeValue
ORDER BY externalCode

Companies has 1.5 million records.
Sales ranges is about 8 records.
Company_industries is just shy of 4 million.

I'll paste the explain I guess, even though it'll be ugly as hell in HTML. But it shows basically what makes sense.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE asr ALL PRIMARY \N \N \N 11 Using temporary; Using filesort
1 SIMPLE c ref PRIMARY,annualSalesRangeId annualSalesRangeId 5 bizcompare.asr.annualSalesRangeId 2 Using where
1 SIMPLE ci eq_ref PRIMARY,companyId PRIMARY 8 const,bizcompare.c.companyId 1 Using index

Any help appreciated, as it's driving me crazy waiting on these pages to run this basic query.

posted by nerhael to Computers & Internet (10 answers total)

Response by poster: Okay, I figured this out shortly after posting. Not sure it's appropriate to ask a follow up that's related.

Basically, the issue is the index it's using for companies is the salesrangeId, which makes pretty much no sense. I've never actually seen mysql do something that stupid. Short of hinting with a USE INDEX(PRIMARY), is there another way I could have written it to have had it work out properly?
posted by nerhael at 10:15 AM on May 6, 2010

WHERE c.annualSalesRangeId = asr.annualSalesRangeId
AND c.companyId = ci.companyId

Maybe swap those two around?
posted by juv3nal at 10:23 AM on May 6, 2010

What are the indexes of the various tables?

Has the amount of data recently increased markedly?, have there been a large volume of changes?

InnoDB or MyISAM?

Try CHECK TABLE, try rebuilding indices

Run the query again without the group by and without the order by then omit group by and order individually i.e. run all 4 permutations of group by and order by
posted by epo at 10:27 AM on May 6, 2010

What juv3nal said, put must 'important' clause first
posted by epo at 10:29 AM on May 6, 2010

erm, "important" s/be "eliminating", I'll stop now
posted by epo at 10:50 AM on May 6, 2010

don't stop now: "must" should probably be "most" ;P
posted by juv3nal at 11:02 AM on May 6, 2010

Response by poster: Clause order made no difference.

Table types: MyISAM.
Indexes have all been rebuilt previously, so are up to date.

asr.annualSalesRangeId is that table's Primary.
c.companyId is primary.
c.annualSalesRangeId is indexed.
ci.industryId/companyId is primary.
ci.companyId is indexed.

Not sure any of the other indexes on companies would be relevant.
posted by nerhael at 11:14 AM on May 6, 2010

create an index :

create index asr_ci on company( annualSalesRangeId, companyId) ;
posted by orthogonality at 12:38 PM on May 6, 2010

MySQL requires an explicit ANALYZE periodically, doesn't it? If you just transferred a database dump from the server to your workstation to test stuff, your workstation's copy of the database might not have accurate statistics about the contents of the tables, leading it to make bad query-planning decisions. (OTOH, I've never been very impressed with MySQL's query planner.)
posted by hattifattener at 2:11 PM on May 6, 2010

Response by poster: Tried adding that index orthogonality, and while it did make it run faster, still 2 seconds vs 50ms when telling it to USE INDEX(PRIMARY). Thought that might work.
posted by nerhael at 9:10 AM on May 7, 2010