Hey, I have a script that caches the output from a query, but whenever the query is run my cpu usage jumps to like 25%

the query (theres about 5 different queries similar to this) is

select n.name, ifnull(c.kills_d,0)+ifnull(s.kills_d,0)+ifnull(r.kills_d,0)+ifnull(k.kills_d,0)+ifnull(o.kills_d,0)  scores, c.online_d+s.online_d+r.online_d+k.online_d+o.online_d online_d from names n
	 left join scores_ctf c on n.id=c.name
	left join scores_slayer s on n.id=s.name
	left join scores_race r on n.id=r.name
	left join scores_king k on n.id=k.name
	left join scores_oddball o on n.id=o.name where n.common=0 order by 2 desc, n.name limit 1;

Basically I need the max combined of a column from 5 different tables, and each entry is not guaranteed to be in every table. I tried creating indexes on the columns, but im not too familiar with index optimization.. can anyone give me some pointers?

8 Years
Discussion Span
Last Post by pritaeas

Make sure you are joining on PK's. Since you check common=0 I'd put an index on that column too.

Additional option could be to create a view to hold all the information, so you could query the view instead of all the tables.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.