What do databases do?
I'm trying to get my team to think only about making the database do what it could do very well. I believe that when they stop thinking of the DBMS as an omniscient, omnipotent being and begin to see it as a useful albeit dumb tool, they can start to approach optimizing and building the database with the right attitude. This got me thinking: What types of operations does a modern database do? I'll start the list with the obvious tasks:
- Joining using indexes
- Filter using indexes
- Minimum Column Order
Any others you can think of?
EDIT: Feel free to add anti-database patterns like:
- Saving and managing binary files
- Hierarchical data processing (most databases)
Picky note: I know the difference between database and DBMS , but most people don't know (or don't bother) to recognize it, so I'm deliberately changing concepts.
source to share
One of the important things that you missed was the aggregate functions : sum, average, minimum, maximum, counter, etc. (at least the database should be really good at doing min, max, count on indexed columns).
source to share
To add to Mark Rushakov's answer, it can manipulate data using multiple aggregates and groupings to format and prepare the required data.
For example, in MySQL, I had a query that would select all users that were logged in and a number that logged for each time of day (24 hour time) over a time period to see when the load was highest.
The database will run faster in these manipulations than the application, but these complex queries are highly dependent on profiling and optimization.
The formatting of the results is important. There is no reason for me to turn the date and time into a string when it can be returned as a string, formatted as needed.
source to share