Efficency

I need some help here, a website I work on had been shut down for running too many slow queries and overloading the server. I need some help here, where can I fund information on making my queries more efficent, or do any of you have any tips? Some of these select queries run on tables with over 10000 entries in them, and I need some advice. Any tips would be appreciated. Thanks

Show some of your queries in this thread. One tip I can give you immediately: if you are using select queries, only select the fields you are going to use. Don't fall into the trap of always using "select * from table".

# User@Host: username @ localhost []
# Query_time: 16 Lock_time: 0 Rows_sent: 0 Rows_examined: 1769

SELECT id, name, description, count(*), count(DISTINCT topicid),
max(timestamp), type,lockedf,theorder
FROM forums, posts where forumid=id group by forumid order by type
desc,forumid;


Thats the query that showed up the most in the slow log(approxamately 100 times in a very short time frame, I just got a shortened bersion of the log) The time on that query runs from 8-20, it varies. Any suggestions?

Yipeeee, that is but a biiiig query.



So, what is problematic about this query? It has three agregate functions, one distinct and one order by. This is extreme load for one query.
But what to do with it? Question is if you really need all this informations. If you really need them, query will be slow. There is no way to avoid it.
So my sugestion is to think carefuly about "what information I really need?". Focus on agregate functions (count, max in your query), distinct and order by operators. Do you really need them all?

you could cache some of the data returned by the query (like that agregate funcitons)

So would it be more efficent to get the info, put into an array, then use PHP to sort it? that would be far less SQL load I beleive, and I have done some stuff with the count, like eliminated it, and the query across multiple tables with the count(I think the mysql manual advises against that). I didn't write most of these queries, this is a mess I have gotten stuck with after the previos programmer made a mess of things. Thanks for the help

Generaly: to do it in PHP would be much much slower. However if load on SQL server is very big, or if SQL server is very slow, it could be better to do it in array or to do part of it in array.