18 seconds to query?!?

rcbasie

Hi.

I've got a rather large problem with MySQL. I'm trying to do a query over several tables, and the query is taking 18 seconds to execute on a 600Mhz sever. For reference, here's the query string....

SELECT tcons.title, tcons.first_name, tcons.last_name, tcons.cons_first4, tcons.cons_spec_code, tcons.cons_seq, thosp.town, thsc.nhs_private, ttrust.region_code
FROM ttrust (INNER JOIN thosp (INNER JOIN tcons INNER JOIN thsc ON tcons.cons_first4 = thsc.cons_four AND tcons.cons_spec_code = thsc.cons_spec AND tcons.cons_seq = thsc.cons_seq) ON thosp.hosp_trust_code = thsc.hosp_trust_code AND thosp.hosp_seq = thsc.hosp_seq)
ON ttrust.trust_code = thosp.hosp_trust_code
WHERE ttrust.region_code = 'LN';

The table thsc has about 40,000 records, tcons about 30,000, thosp about 4000 and only a couple of hundred in ttrust, and the resulting query has about 7500 rows in it.

Is there any way that i can speed up these queries? I havent defined any forign keys on the tables, or the table type - does this matter? I really hope this isn't a limitation of MySQL, cos that would screw up my plans for my website!

Please don't let it be true!
Thanks
Robin

el_kab0ng

mysql> create index index_name on table(fieldname);

creating indexes greatly improves the speed at which your queries will complete.

http://www.mysql.com/doc/en/CREATE_INDEX.html

rcbasie

Thanks for your response.

I've already set up the table with primary keys, and all of the fields in the join are defined as primary keys - does this mean that they're indexed automatically? Also, the thsc table has a 6 field primary key, will this slow it down?

siwis

I'd suggest that you take a problem like this to the real people in the know by asking your question on the MySQL support forums.

el_kab0ng

no. primary keys != indexing.

Indexing basically builds a quick list of the fields you specify that mysql checks first before running a table wide search. This has helped me, and I have ~1 million records I go through in about .03 seconds. I have 3 indexes built that get queried the most.

Be sure that when you update your data, you update the indexes too if those fields get altered.

rcbasie

Thank you, that's really useful info!

mrmufin

And remember, the columns which should be indexed are typically those which are follow a "WHERE" clause...

Just my 2 cents...

ultraslacker

Posting the EXPLAIN with a query is helpful - I assume you checked it and know it is using indexes.

For many cases, it helps to join on smaller tables first, then include larger ones - have you tried rearranging your join order? You may be pulling out way too many rows initially that could be avoided by building up the WHERE criteria before hitting the larger tables.

Mysql does tend to slow down with joins, while it is very fast on denormalized tables.

ultraslacker

no. primary keys != indexing.

I know mysql does some freaky stuff, but this isn't one of them! Primary key has to be indexed in order to maintain its integrity.

mnobvibjunr

fgcpzfjhbubstnlzvxkkkrsalry, <a href="http://theprofitspy-reviews.net/">Amazon Coupons</a>, etlbenypz, Amazon Discount, qSXuqeuyz, http://theprofitspy-reviews.net/ Amazon Discount Coupons, siImriktd, <a href="http://thesimplegolfswing-scam.net/">Amazon Gift Card</a>, cnYfpUkPL, Amazon Discounts, GzPZUpyUu, http://thesimplegolfswing-scam.net/ Amazon Deals, BIqfGrWrV, <a href="http://themagicofmakingup-scam.net/">Amazon Coupon</a>, hnMulKGDQ, Amazon Discount Code, XPCqUojJy, http://themagicofmakingup-scam.net/ Amazon Discount Codes, saZecarwc, <a href="http://thesixfigurecode-reviews.net>Amazon Christmas</a>, fHbrTleye, Amazon Christmas Presents, BTMmPiprN, http://thesixfigurecode-reviews.net/ Amazon Christmas Deals, SzCyRHNeu, <a href="http://whitehatcopycat2-reviews.net/">Amazon Gifts</a>, aITVnndub, Amazon Christmas Ideas, THCAZUSEq, http://whitehatcopycat2-reviews.net/ Amazon Cheap Deals,WnDbywpJN, <a href="http://pregnancysymptomssigns.net/">Pregnancy Symptoms</a>, AzDemrzZW, Pregnany Week by Week, XvpuseOpx, http://pregnancysymptomssigns.net/ Pregnancy Calculator, RnDZmgEBa.