how to test your mysql queries?

Question

I have developed some mysql queries for my application, and created indexes and used EXPLAIN statements as well.

• What types of testing methods we can use to check queries (performance testing, load testing, concurrency testing and others)

• How to use those testing methods in your system, anything related to query testing is helpful to me.

Thanks in advance.

Answer 1

Test with sysbench , it is a great tool for simulating database traffic. Furthermore I would suggest getting familiar with the MySQL EXPLAIN statement as it helps to dissect a query and improve on slow areas.

There are quite a few articles on the internet that explain how to properly benchmark, here is just one of them http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/ .

Last but not least there is no substitute for testing with real data. Theoretically speaking certain queries should handle better than others, but the only sure way to know this is by testing your schema with actual data. There is a handly tool named generatedata that creates a lot of dummy data so that you can perform said tests.

In order to properly benchmark your queries you must ensure any cached queries and database information is wiped so that the result times are accurate and independent of one-another; you can do this by performing a RESET QUERY CACHE and FLUSH TABLES before running each query.

Additional information as requested: From experience the best way to handle concurrency is by using the MySQL SET TRANSACTION statement to properly isolate your queries. By using the InnoDB engine the database will perform row locking which is often sufficient for most applications. You can test this by performing equivalent tasks on the database but with separate transactions. Concurrency is a very broad topic in the database world and I would highly recommend further researching this topic.