I've been working with Alpha software for over 5 years. I am well versed in the application. Recently, I started looking into optimizing my database trying to make queries quicker. I run MariaDB 10, a popular and binary compatible fork of MySQL. But the following will apply to MySQL as well. (Alpha doesn't even know the difference between the two, uses the exact same connection string). Anyways, I turned on Query logging and took a look at some statistics of queries run. Over 50% of my queries are SET option queries.
It looks like there are at least 3 queries that are run on every single connection to the database:
1) Sets character_set_results to utf8
2) Sets character_set_client to utf8
3...N) Runs selected queries and finally, closes the connection.
First: utf8 is actually feature incomplete on MySQL. If you want utf8, then you should be using utf8mb4. (See: https://mathiasbynens.be/notes/mysql-utf8mb4). So if Alpha software is trying to get full utf8 support from MySQL, it's missing the mark.
Second: If that is the case, a single query would suffice. SET character_set_results=utf8, character_set_client=utf8.
Third: My database is already setup to properly support utf8. So these two queries don't actually change anything.
Suggestion: Add something in the connection string that tells Alpha that my database is already encoded in utf8, then be able to skip those two queries in each connection.
Second suggestion: If that can't be done, at least combine them into a single query.
Caveat: I know we're talking about thousandth's of a second with the SET queries. Sure it adds up over millions of connections, probably to something insignificant, but it would lower I/O on my server. At the most, this might reduce the number of queries down 2/3rds.
Thanks for listening
Code:
5639 Connect root@localhost as anonymous on baltimore 5639 Query SET character_set_results=utf8 5639 Query SET character_set_client=utf8 5639 Query SELECT DISTINCT name, id FROM cs_territories WHERE id > 0 ORDER BY id, name 5639 Quit
1) Sets character_set_results to utf8
2) Sets character_set_client to utf8
3...N) Runs selected queries and finally, closes the connection.
First: utf8 is actually feature incomplete on MySQL. If you want utf8, then you should be using utf8mb4. (See: https://mathiasbynens.be/notes/mysql-utf8mb4). So if Alpha software is trying to get full utf8 support from MySQL, it's missing the mark.
Second: If that is the case, a single query would suffice. SET character_set_results=utf8, character_set_client=utf8.
Third: My database is already setup to properly support utf8. So these two queries don't actually change anything.
Suggestion: Add something in the connection string that tells Alpha that my database is already encoded in utf8, then be able to skip those two queries in each connection.
Second suggestion: If that can't be done, at least combine them into a single query.
Caveat: I know we're talking about thousandth's of a second with the SET queries. Sure it adds up over millions of connections, probably to something insignificant, but it would lower I/O on my server. At the most, this might reduce the number of queries down 2/3rds.
Thanks for listening
Comment