MySQL vs FILE

I have a php script in my site which is getting accessed 31k everyday. Now there are two/three sql commands which run in that script. Since it was taking too much system resources, it got suspended in the hosting site.

The three sql commands are

Now I can remake things such that I can get rid of MySQL, but will file handling will reduce the system resource or will it increase the system resource?

Is there any other solution apart from upgrading the hosting server?

I can not cache it because I need to display the updated value.

If you do the same thing but with files, it will most likely be worse for you. What type of MySQL table are you using? For instance, MyISAM? InnoDB? The reason I ask is it looks like you may be doing more writes to the table than using reads. Certain table types will handle better than other table types depending on your circumstances. MyISAM tables are best if the majority of your SQL queries are reads. InnoDB is better if the majority of your SQL queries are writes.

Second, do you have indexes on these tables? In your first two SQL queries you are using WHERE. If you are not using indexes properly on your tables that could slow things down quite a bit and increase your resource usage.

So I would check the type of tables you are using, and make sure you have setup indexes on your tables correctly to keep your queries efficient.

Thx for your reply.

I am using MyISAM engine. I didnt check that. Do you think the last query (which is an Insert query) having MyISAM engine causing problem to the script?

I keep Atleast one Primary key for Where queries. So i have indexes but i think i need to check if that is proper or not.

It just depends, you may want to try InnoDB to see if that works better for your situation. As far as the indexes, you can tell if your queries are efficient by putting the word "Explain" in front of any of your queries and seeing the results. It will tell you how many records it has to go through, and the search type. You can learn more about Explain here:

http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/ ... plain.html

Also the join type is important with system, const, eq_ref, and ref being the best. If you see "All" that is usually not good.

You may want to look at a few other Ozzu threads that talk about optimizing how you use MySQL:

programming-forum/what-are-lot-mysql-queries-per-page-t83201-15.html

Why do I get the feeling that you are doing this in some form of PHP loop? Looking at that code even if it was executed 31000 times a day shouldn't cause the kind of load that would cause the host to suspend it. If a day consists of 86400 seconds and your script is being hit 31000 times it means that it is is being executed every 2.7 seconds. Looking at that code above it shouldn't take longer than 2.7 seconds.

What is the PHP code doing with these sql statements?

nice math but isn't it why we get bigger servers to handle the load, but i still see your point in the loop tho. Need to strip down the code for less serverload.