From BC$ MobileTV Wiki
Jump to: navigation, search

A DataBase Management System (commonly abbreviated DBMS) is an application or tool which aids in the storage, manipulation, reporting, management, and control of data.



Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by".[1]


Views are essentially queries that run before a WHERE clause (in the case of a TEMPTABLE where no INDEXes are carried over to the temporary table) or get expanded (in the case of a MERGE operation where ALIAS values are per-filled to expand out the query). Views are best used to join multiple tables into a single unified table, but depending on the DBMS can cause performance problems if over-used or if used on database tables with more than 100k rows.[3][4][5]

Stored Procedure



Atomicity, Consistency, Isolation, Durability (ACID) is one of the key features of traditional DBMS which implement the SQL standard, such as an RDBMS. It ensures that data will be





  • The Sakila Database: https://www.jooq.org/sakila | SRC (one of the best example databases out there - originally created by MySQL team - is now OSS under BSD License, offering a nicely normalised schema to model a DVD rental store, featuring: films, actors, film-actor relationships, and a central inventory table that connects films, stores & rentals)[12]


External Links


  1. When To Use Indexes In MySQL: http://www.howtoforge.com/when-to-use-indexes-in-mysql-databases
  2. Create and use an index to improve performance: http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspx
  3. When to use views in MySQL?: http://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql
  4. MySQL VIEW as performance troublemaker: http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
  5. When to use R, when to use SQL?: http://stackoverflow.com/questions/9795051/when-to-use-r-when-to-use-sql
  6. SQL Server Views: http://odetocode.com/articles/299.aspx
  7. SQL & Stored Procedure Optimization tips: http://moondev.wikidot.com/sql-optimization
  8. Stored Procedures are EVIL: http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
  9. How-to generate Insert statements Stored procedure for SQL server: http://ysgitdiary.blogspot.ca/2010/03/how-to-generate-insert-statement.html
  10. SQL Optimization Tips and Index Optimization tips - TRIGGERS AND VIEWS: http://www.computeruser.com/tutorials/sql-optimization-tips-and-index-optimization-tips/triggers-and-views.html
  11. Say What? RDBMSs are Not Legacy Technology: http://java.dzone.com/articles/say-what-rdbmss-are-not-legacy
  12. Use MULTISET Predicates to Compare Data Sets: https://blog.jooq.org/use-multiset-predicates-to-compare-data-sets/

See Also

Database | SQL | MySQL | RDBMS | Optimization | Hibernate | JPA | DBCP