Query running fine on home server but crashing site's server.

[JoaoJose]

Hi.

I've got a tricky problem on hands. I have one particular query which is running @0.2329 sec on my home server. Not particulary fast but good enough. My problem is it's eating up to much CPU time on my site's server and getting me suspended.

Log gives :
Mon Aug 14 12:22:07 2006: used 1026.00 seconds of cpu time for [[mysql query]]

This is the query:

SELECT d.field, d.field, d1.field AS xfield, d2.field AS xfield, d.field, d.field, d.field, d.field, d.field, d.field, d4.field AS xfield, d5.field AS xfield, d6.field AS xfield, d3.field, d3.field, d3.field, d7.field AS xfield, d8.field AS xfield, d8.field AS xfield
FROM table d
LEFT JOIN table d1 ON d.field = d1.field
LEFT JOIN table d2 ON d.field = d2.field
LEFT JOIN table d3 ON d.field = d3.field
LEFT JOIN table d4 ON d3.field = d4.field
LEFT JOIN table d5 ON d3.field = d5.field
LEFT JOIN table d6 ON d3.field = d6.field
LEFT JOIN table d7 ON d.field = d7.field
LEFT JOIN table d8 ON d3.field = d8.field
WHERE (
field =1
)
ORDER BY xfield, xfield, field ASC

By doing an EXPLAIN query this is what I get:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE d ALL NULL NULL NULL NULL 2886 Using where; Using temporary; Using filesort
1 SIMPLE d1 eq_ref PRIMARY PRIMARY 4 database.d.field 1
1 SIMPLE d2 eq_ref PRIMARY PRIMARY 4 database.d.field 1
1 SIMPLE d3 eq_ref PRIMARY PRIMARY 4 database.d.field 1
1 SIMPLE d4 eq_ref PRIMARY PRIMARY 4 database.d3.field 1
1 SIMPLE d5 eq_ref PRIMARY PRIMARY 4 database.d3.field 1
1 SIMPLE d6 eq_ref PRIMARY PRIMARY 4 database.d3.field 1
1 SIMPLE d7 eq_ref PRIMARY PRIMARY 4 database.d.field 1
1 SIMPLE d8 ALL NULL NULL NULL NULL 2

I can see I could improve table d8 by adding an index on the field I'm using. But other than that I can't see anything more I can do.

What's your thoughts about this?

[FalseDawn]

Index all fields that form part of a JOIN - i.e. all fields named "field" - not sure if this is actually the name of the field or a generic name you have used for illustration. It's not very descriptive!

Check your ORDER BY - it doesn't look correct, with a repeated field name (or is this because they aren't the real field names?)

What is the query supposed to accomplish anyway? How many records are in the tables?

[JoaoJose]

Yes "field" is a generic name I've used. Anyway this is just a query to get results from table d . The joins only exist to assign a name to a field on table d.

For example I may have d.color=3 and then make a join to get the particular name to that color.

ex:
SELECT d.color, d1.name FROM tshirts d LEFT JOIN colors d1 ON d1.id=d.color

Anyway I was hoping someone have had this problem before as this query works with no problem on my home server.

Thanks for your help.

[FalseDawn]

Mon Aug 14 12:22:07 2006: used 1026.00 seconds of cpu time for [[mysql query]]

Is that for just ONE execution of the query, or cumulative over ALL executions? I assume it's the latter, as I can't see it taking almost 20 minutes to run one query.

Is this a query that is executed frequently on your site?

If you can't get it resolved, you might need to look into a better server with higher guaranteed resources.

[JoaoJose]

Problem solved. I just had to add an index to table d8 as it was the only one that wasn't joining on it's index field. Anyway I'll have to think about getting a VPS in a couple of months.

Thanks for your help FalseDawn!