MySQL "Conueries": Is my database innefficient?


#1

Greetings,

I do not know much about measuring database efficiency and I was checking my site statistics to see how much resources I am using up. The problem is I do not understand the figures when it comes to databases.

I am worried because I built my first-ever MySQL/PHP site so I’m not sure if my code is crap or if I’m bogging down the servers and at risk of being booted onto a dedicated server. I am also starting to get about 100 visitors per day to my site, which is completely dependent on the database and uses a lot of information from it.

Here is my statistics report:

Please interpret the results for me and let me know the condition of my database efficiency.

Kind regards


#2

It’s good that you’re thinking in this way, but it’s hard to tell if your application is efficient or not based solely on that data, as it also depends on how much traffic you’re getting, how your queries are constructed, and how “necessary” each query is.

Just looking at the queries to connection ratio alone, it looks to be fine since your ratio of queries to connections is way above 1. But that doesn’t say anything about whether you’re executing unnecessary queries, which itself could contribute to really high query counts. Without know what your app does, it’s impossible to know if that number of queries or amount of data being transferred is appropriate.

But with 100 visitors per day, you shouldn’t have any problems as long as you don’t do anything really stupid. However, if you’re worried about database efficiency or performance, you should look into caching, indexing, and slimming down your queries where possible. Also look out for ways to refactor your queries, profiling them to see how the performance compares.

And one thing that trips up a lot of developers new to SQL is the use of single record inserts when they’re trying to do a batch import or otherwise create thousands of records at once. If you instead combine all of those queries into a single INSERT statement, you can see huge performance increases. If you’re inserting tens of thousands of records or more, then you ought to try LOAD IN FILE, which is even faster than bulk inserts.