Why do people use MySQL again?
- August 6th, 2010
- By Richard
- Write comment
I haven’t worked with MySQL in quite a while, and lately I have been doing heavy amounts of Transact SQL programming with MSSQL for my work. So when a friend of mine asks me a question about a MySQL query he wrote and I reply with “Well, what does the execution plan look like?”, I was rather surprised when he reminded me that execution plans in MySQL are all determined at runtime by the MySQL optimizer so they are semi random. I asked him to explain and he did, and what he described absolutely boggled my mind as to why people tolerate MySQL.
Now, correct me if I’m wrong with this but, with MySQL there is no way to create stored procedures like you can with MSSQL (Stored procedures are basically queries that are optimized and compiled and stored as database objects that you can execute and pass variables into, sort of like UDF’s except they aren’t performance hogs like UDF’s are). This means that all of the MySQL queries are essentially inline SQL in whatever code you are developing, and have to be optimized and compiled at runtime (This also can cause problems for code maintenance if you have similar queries that are spread over say 20 different files, although this can be made easier if you use some sort of SQL query handler object that builds the simpler queries based on arrays of data you pass to it). Now, as my friend explained, the problem with MySQL is that the optimizer will sometimes look at the records and if it determines that enough of the data is similar in a certain column it will ignore the index entirely. This basically results in semi-random execution plans since the index might change based off of the records being used.
I tried to find some articles online that explain similar issues to this with MySQL but I was unable to, but if this is true I might have to avoid using MySQL in the future because having to deal with that would be a nightmare.