MySQL Query Performance Improvements

SoftTester uses a program written in VB.Net to perform all of the transactions. At the moment.t this runs during the night. In the last few months is undergone some big changes moving from MS Access offline with My SQL, to completely with MySQL.

Obviously when I did this conversion I had to do it in stages and leave tweaks till last after things settled down.

The next thing I need to do is query tweaks to improve the performance of the program and the MySQL process on the web server. Then I can do more with the program and hopefully convince my ISP that the program is fast enough to run during the day OR as he prefers in realtime.

Following a conversation with my ISP about the queries the program uses, I thought i'd share the I'd share the bigger picture.

Try to avoid text searches, use number / indexes fields.
When you have to use text searches try to include another clause to cut down that amount of records that the database has to look at to provide results.

This is a problem for me as I search on pad url as it is unique. I don't have any other data which is unique.

Next, instead of having big update queires which update fields which are already up-to-date, store the data in variables or some sort of structure and produce a smaller update query.

Then I have in my main table what I call a locking flag. This allows me to gain the key value after the record is added. Instead I can do a Max on key field plus one, then I have the record key id. Oh of course I use auto num.

Its a start but I'm hopeful these will make a big difference

by JM