3 ms·
The meta-point for me is that SQL is just broken, as are its many implementations. We'd never tolerate a scripting language that made you learn all the implemen
by mkn 18y ago
The meta-point for me is that SQL is just broken, as are its many implementations. We'd never tolerate a scripting language that made you learn all the implementation details before you were effective with it. And yet we tolerate this from database engines and the languages we use to interact with them.
The opposite end of the spectrum is modern compilers. C/C++ compilers are generally so good that most attempts at nuanced optimization are just pointless; The compiler saw you coming and optimized your code behind your back before you got there. Why can't storage engines be like this? Why do you have to think not only about the type of your data, but how you want it implemented as well? Are there technical reasons why a db can't take generic data declarations ('string' instead of 'varchar(255)') and do the right thing with it when you populate your db? Or treat the use of a transaction as a hint that you'd like a storage engine that supports transactions?
I realize there comes a point where the db design has to be clamped down for production, but in the design stages there's a lot of optimization that a 'SQL compiler' could do behind your back. It currently seems like database product designers have taken the lazy way out and decided that optimization should live in the developers' heads rather than in the engine.
It just seems like SQL is this awful holdover from the days of COBOL and that we seriously need a modern product that let's us think about our programming problems rather than SQL's hangups. Am I missing something really basic, here?
Let me emphasize that, afaik, ronaldbradford knows his stuff, and I'm glad for him if he can make a living off of his MySQL knowledge. I just wish that that knowledge was embedded in the products themselves.
- newt0311 18y agos/SQL/MySQL Other dbs like oracle, db2, postgres, and sql server are indeed much better about this. Ie. they only require optimizations on statistics like resident memory, etc... The actual queries do not need to be optimized except in extreme cases.
- gaius 18y agoI just wish that that knowledge was embedded in the products themselves Err, it is. Oracle, Sybase, MS SQL, Informix, all the major database products have sophisticated query optimizers. You use SQL to describe the result set you want, and it figures out the best way to get it. Sadly the Web 2.0 world is full of people who actually believe MySQL is as good as databases get, having never used anything else. It's probably a good 10-15 years behind the state-of-the-art, at least (and Oracle et al are behind in niche areas).
- newt0311 18y agoAgreed. A single look at a performance chart for MySQL under simultaneous transaction load shows it crumbling under. Furthermore, it has had substandard ACID support and substandard ANSI SQL support for generations and these problems are just starting to get fixed. Using MySQL as an indicator for the rest of the industry is deeply misguided.
- mkn 18y agoI hear the points about query optimizers that you and the other repliers have made. (And they are valid points, all. I was just misinformed about MySQL.) I was more addressing the points made about database design decisions w/respect to data type choice and their effect on storage requirements and speed. I was wondering, specifically, if it is technically feasible to allow designers to specify 'text' or 'string' at design time, enter in some typical data, and have the engine choose the optimum type subject to its implementation constraints. That said, it seems like I need to educate myself on the various products, though any prototypes I build will still use MySQL just because of the price point. :o)
- gaius 18y agoCheck out PostgreSQL, Firebird and SAP/DB. I can't think of a single technical or commercial reason to use MySQL.
- newt0311 18y agoCorrection. There is one reason. Its a lot easier to find MySQL devs. than postgres, firebird, or SAP/DB devs even though the latter are a far superior product technically.
- newt0311 18y agoTo give an example on varchars. Postgres never uses more space for varchars than strictly necessary. In fact, it is common to use text columns which are varchars extended to 2 gigabytes. Furthermore, postgres is capable of automatically compressing and decompressing data on the fly, no interaction required. It also has specialized data types for pretty much any task you can imagine and a very robust extension system in case you need to roll your own types.