sqlite - MySQL: How to get the modularity of views, but the optimization of longer code? -


in both mysql , sqlite3, 1) writing queries nested views slower copying code view , pasting larger query (source: here, here, here, personal experience). however, 2) how views make sql code shorter , more modular, in same way functions make code shorter more modular in other languages. finally, 3) lazy, , want optimizer work of planning joins, picking indices, , finding efficiencies me. suspect if wanted hands dirty , plan joins myself, write queries both modular , fast, don't want that.

therefore, of 1) speed, 2) modularity, , 3) ease, seems can have two. how can have three?

in mysql, ordinarily query planner handles query constructed views , 1 constructed nested subqueries same way. mysql, unlike other makes , models of rdms, doesn't have built-in materialized views.

but, if want guarantees of 3 of speed, modularity, , ease, you're going have switch rdms. oracle of want, working you'll have hire dba , pay big licensing fees.

common table expressions way modularize code. postgresql handles those; commercial rdmss.


Comments