在重负载下检索mysql数据的正确方法

Question

I have build a system using PHP-MySQL. This system is subject to a very heavy load, with thousands of selects,updates,inserts,deletes every minute.

I would like to optimize this system, to make it faster, and reduce load on the servers. I have already introduced memcache, but mysql data is still needed.

So my question is, which method would be the best in this case. Currently my queries would look like this:

$q = mysql_query($sql);
while(mysql_fetch_array($q)) {...

I have read that there is a little speed to gain by using mysql_fetch_assoc (?)

But perhaps there is an antirely different approach, when i start optimizing this system?

Thank you all - (Apologies for my limited english skills)

Answer 1

mysql_fetch_assoc vs mysql_fetch_array will duplicate less data thus use less memory. Since the data is presented associative and by index in the array, with that you will get some tiny optimization although will help if your dataset is big.

• Try to use natural sort (AKA avoid SORT in query sentences) and LIMIT your result set if you can
• Batch queries: instead run 100 inserts over the same table try to do a few of them small.
• cache cache cache if you can: using redis or memcached.
• if you generate pages that can be treated as static try to use HTTP headers to avoid browsers to request your site all the time
• etc. etc.