Optimising MySQL Query - beginners Q

[craigy101]

Hello there, this is a MySQL question but also a beginners question as I am not really a PHP developer at all.

I run a website that is mostly DB driven and at peak times the site grinds to a halt. After plenty of testing and debugging between the web host and myself it appears the MySQL queries to my DB need “optimising”. The web host stated that my MySQL queries were unoptimised and hanging which resulted in slower performance, and which has started to also affect my SERP results. Not good.

After Googling for help it appears that most of the websites are far too advanced for me as most of it went over my head.

Below is the code for how I connect to my database then storing the resulting array into a variable,

[php]mysql_connect($host, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());
$query = “SELECT id, txtitem2, txtitem3 FROM mytable WHERE type = ‘A String’ OR type = ‘A String 2’ OR type = ‘A String 3’ ORDER BY id DESC LIMIT 5”;
$result = mysql_query($query);[/php]

What way would be best to optimise this code to prevent the site from crashing? Ideally I am looking for some code to copy and paste along with an explanation as to what it does (i like to know what each line does as opposed to blindly copying and pasting) - any advice would be greatly appreciated!

Thanks
Craigy

[smokeys_13]

Well that query barely needs optimising - I’ll assume there are others. If the site’s reaching high numbers of connections (or the host just isn’t that great) you will want to cache your queries wherever possible. If queries are the same every time or there are a number of similar variations of a query, wherein the results will be given to many if not all visitors - it is pointless doing a fresh query every page request. Server-side caching either in a file, or in the server’s memory (whichever is possible and easiest) will mean visitors will instantly be served the latest up-to-date response from that query, once someone visits the site 3 hours after the cache was last updated (as an example), the query will be run again, and the data will replace the cache, ready to be served to the next X visitors.

I imagine this method has probably come up on your searches, is there a part of the method that you don’t understand, don’t know where to start with it?

If the above is possible in any way it will definitely help as it reduces the amount of times your web server has to ‘to & fro’ with the MySQL server.

[craigy101]

Thanks for the reply. Part of the site runs on WPress so I have been deleting revisions/optimising database, stuff like that.

Caching makes sense to me, as in I understand what you mean, but cannot figure out how to do it… as in what code I need to write.

Yes there are multiple queries across the site, one per page, with slightly different criteria, (i.e. pulling data from different tables, under different criteria, or even adding/editing the database)

The host is bluehost, who are quite reputable, and much better than my last host who were useless. However this has happened over multiple hosts now, during peak times, i.e. when I make a FB post linking to my site)

Any advice on caching would be greatly appreciaited. During my research I also came across putting a Max. time limit on queries so they don’t hang, is this something you’d recommend?

Thanks.