SQL questions

Hey all,

I just have a couple questions regarding SQL (using mySQL v4).

1) how many SQL queries is acceptable when rendering a PHP page. I know this is kind of a vague question and it depends on a lot of things... but i was just wondering when I am developing, when should I draw the line with the queries.

2) I use a lot of counters on my page and display stats in various ways. I was wondering if there is an efficient way to sort records in a query like this:

"SELECT id,COUNT(*) as myCount FROM table WHERE type='nice' GROUP BY date ORDER BY myCount DESC"

The key being the 'ORDER BY myCount' where myCount is the count value column.

I did an EXPLAIN on a few of my queries and am getting 'Using temporary, Using filesort' which from what i read is what you want to avoid.


3) is one big slow SQL statement better then 10 small fast ones?

thanks in advance!

-holo

Not directly answering your question.. but...

You may want to think about a more efficient design. Searching for 'nice' is slower than having a numeric ID. type=0 i.e. 0 = Nice, 1 = Bad.. whatever in a lookup table.

You might want to also shorten ordering field..
http://www.mysql.com/doc/en/BLOB.html

"Because BLOB and TEXT values may be extremely long, you may run up against some constraints when using them:

If you want to use GROUP BY or ORDER BY on a BLOB or TEXT column, you must convert the column value into a fixed-length object. The standard way to do this is with the SUBSTRING function. For example:
mysql> SELECT comment FROM tbl_name,SUBSTRING(comment,20) AS substr
-> ORDER BY substr;
If you don't do this, only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024; this value can be changed using the -O option when starting the mysqld server. You can group on an expression involving BLOB or TEXT values by specifying the column position or by using an alias:
mysql> SELECT id,SUBSTRING(blob_col,1,100) FROM tbl_name GROUP BY 2;
mysql> SELECT id,SUBSTRING(blob_col,1,100) AS b FROM tbl_name GROUP BY b;
The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can actually transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size (max_allowed_packet), but you must do so on both the server and client ends. See section 5.5.2 Tuning Server Parameters.
"

You may notice some performance increase, maybe not.


Trevor