Database development mistakes made by application developers

Question

What are common database development mistakes made by application developers

User · Answer

I think the biggest mistakes that all developers and DBAs do is believing too much on conventions  What I mean by that is that convention are only guide lines that for most cases will work but not necessarily always  I great example is normalization and foreign keys  I know most people wont like this  but normalization can cause complexity and cause loss of performance as well  so if there is no reason to move a phone number to a phones table  don t do it  On the foreign keys  they are great for most cases  but if you are trying to create something that can work by it self when needed the foreign key will be a problem in the future  and also you loose performance  Anyways  as I sad rules and conventions are there to guide  and they should always be though of but not necessarily implemented  analysis of each case is what should always be done

User · Answer

In my experience  Not communicating with experienced DBAs

User · Answer

Biggest mistake is having a loop in the code updating or inserting data when a simple set-based solution would do the trick much faster  and much more simple

User · Answer

This has been said before  but   indexes  indexes  indexes   I ve seen so many cases of poorly performing enterprise web apps that were fixed by simply doing a little profiling  to see which tables were being hit a lot   and then adding an index on those tables   This doesn t even require much in the way of SQL writing knowledge  and the payoff is huge   Avoid data duplication like the plague   Some people advocate that a little duplication won t hurt  and will improve performance   Hey  I m not saying that you have to torture your schema into Third Normal Form  until it s so abstract that not even the DBA s know what s going on   Just understand that whenever you duplicate a set of names  or zipcodes  or shipping codes  the copies WILL fall out of synch with each other eventually   It WILL happen   And then you ll be kicking yourself as you run the weekly maintenance script   And lastly  use a clear  consistent  intuitive naming convention   In the same way that a well written piece of code should be readable  a good SQL schema or query should be readable and practically tell you what it s doing  even without comments   You ll thank yourself in six months  when you have to to maintenance on the tables    SELECT account number  billing date FROM national accounts  is infinitely easier to work with than  SELECT ACCNTNBR  BILLDAT FROM NTNLACCTS

User · Answer

Key database design and programming mistakes made by developers   Selfish database design and usage   Developers often treat the database as their personal persistent object store without considering the needs of other stakeholders in the data   This also applies to application architects   Poor database design and data integrity makes it hard for third parties working with the data and can substantially increase the system s life cycle costs   Reporting and MIS tends to be a poor cousin in application design and only done as an afterthought  Abusing denormalised data  Overdoing denormalised data and trying to maintain it within the application is a recipe for data integrity issues   Use denormalisation sparingly   Not wanting to add a join to a query is not an excuse for denormalising  Scared of writing SQL   SQL isn t rocket science and is actually quite good at doing its job   O R mapping layers are quite good at doing the 95  of queries that are simple and fit well into that model   Sometimes SQL is the best way to do the job    Dogmatic  No Stored Procedures  policies   Regardless of whether you believe stored procedures are evil  this sort of dogmatic attitude has no place on a software project    Not understanding database design   Normalisation is your friend and it s not rocket science   Joining and cardinality are fairly simple concepts - if you re involved in database application development there s really no excuse for not understanding them

User · Answer

Number one problem  They only test on toy databases  So they have no idea that their SQL will crawl when the database gets big  and someone has to come along and fix it later  that sound you can hear is my teeth grinding

User · Answer

Many developers tend to execute multiple queries against the database  often querying one or two tables  extract the results and perform simple operations in java c c   - all of which could have been done with a single SQL statement    Many developers often dont realize that on development environments database and app servers are on their laptops - but on a production environment  database and apps server will be on different machines  Hence for every query there is an additional n w overhead for the data to be passed between the app server and the database server  I have been amazed to find the number of database calls that are made from the app server to the database server to render one page to the user

User · Answer

There is one thing I might add  learn using analytic functions like PARTITION BY  RANK  DENSE RANK  for Oracle   They are absolutely essential for complex queries     Other advice is  if possible  to have a dedicated database developer in your development team who is expert in SQL  database modelling  tuning  etc   Not a DBA though   Such skill is a great asset

User · Answer

a  Hardcoding query values in string b  Putting the database query code in the  OnButtonPress  action in a Windows Forms application  I have seen both

User · Answer

Forgetting to set up relationships between the tables  I remember having to clean this up when I first started working at my current employer

User · Answer

Treating the database as just a storage mechanism  i e  glorified collections library  and hence subordinate to their application  ignoring other applications which share the data

User · Answer

1  Not using appropriate indices  This is a relatively easy one but still it happens all the time   Foreign keys should have indexes on them   If you re using a field in a WHERE you should  probably  have an index on it   Such indexes should often cover multiple columns based on the queries you need to execute   2  Not enforcing referential integrity  Your database may vary here but if your database supports referential integrity--meaning that all foreign keys are guaranteed to point to an entity that exists--you should be using it   It s quite common to see this failure on MySQL databases   I don t believe MyISAM supports it   InnoDB does   You ll find people who are using MyISAM or those that are using InnoDB but aren t using it anyway   More here    How important are constraints like NOT NULL and FOREIGN KEY if I   ll always control my database input with php  Are foreign keys really necessary in a database design  Are foreign keys really necessary in a database design    3  Using natural rather than surrogate  technical  primary keys  Natural keys are keys based on externally meaningful data that is  ostensibly  unique   Common examples are product codes  two-letter state codes  US   social security numbers and so on   Surrogate or technical primary keys are those that have absolutely no meaning outside the system   They are invented purely for identifying the entity and are typically auto-incrementing fields  SQL Server  MySQL  others  or sequences  most notably Oracle    In my opinion you should always use surrogate keys   This issue has come up in these questions    How do you like your primary keys  What s the best practice for primary keys in tables  Which format of primary key would you use in this situation  Surrogate vs  natural business keys Should I have a dedicated primary key field    This is a somewhat controversial topic on which you won t get universal agreement   While you may find some people  who think natural keys are in some situations OK  you won t find any criticism of surrogate keys other than being arguably unnecessary   That s quite a small downside if you ask me   Remember  even countries can cease to exist  for example  Yugoslavia    4  Writing queries that require DISTINCT to work  You often see this in ORM-generated queries   Look at the log output from Hibernate and you ll see all the queries begin with   SELECT DISTINCT       This is a bit of a shortcut to ensuring you don t return duplicate rows and thus get duplicate objects   You ll sometimes see people doing this as well   If you see it too much it s a real red flag   Not that DISTINCT is bad or doesn t have valid applications   It does  on both counts  but it s not a surrogate or a stopgap for writing correct queries   From Why I Hate DISTINCT      Where things start to go sour in my   opinion is when a developer is   building substantial query  joining   tables together  and all of a sudden   he realizes that it looks like he is   getting duplicate  or even more  rows   and his immediate response   his    solution  to this  problem  is to   throw on the DISTINCT keyword and POOF   all his troubles go away    5  Favouring aggregation over joins  Another common mistake by database application developers is to not realize how much more expensive aggregation  ie the GROUP BY clause  can be compared to joins   To give you an idea of how widespread this is  I ve written on this topic several times here and been downvoted a lot for it   For example   From SQL statement -    join    vs    group by and having         First query   SELECT userid FROM userrole WHERE roleid IN  1  2  3  GROUP by userid HAVING COUNT 1    3       Query time  0 312 s      Second query   SELECT t1 userid FROM userrole t1 JOIN userrole t2 ON t1 userid   t2 userid AND t2 roleid   2 JOIN userrole t3 ON t2 userid   t3 userid AND t3 roleid   3 AND t1 roleid   1       Query time  0 016 s      That s right  The join version I   proposed is twenty times faster than   the aggregate version    6  Not simplifying complex queries through views  Not all database vendors support views but for those that do  they can greatly simplify queries if used judiciously   For example  on one project I used a generic Party model for CRM   This is an extremely powerful and flexible modelling technique but can lead to many joins   In this model there were    Party  people and organisations  Party Role  things those parties did  for example Employee and Employer  Party Role Relationship  how those roles related to each other    Example    Ted is a Person  being a subtype of Party  Ted has many roles  one of which is Employee  Intel is an organisation  being a subtype of a Party  Intel has many roles  one of which is Employer  Intel employs Ted  meaning there is a relationship between their respective roles    So there are five tables joined to link Ted to his employer   You assume all employees are Persons  not organisations  and provide this helper view   CREATE VIEW vw employee AS SELECT p title  p given names  p surname  p date of birth  p2 party name employer name FROM person p JOIN party py ON py id   p id JOIN party role child ON p id   child party id JOIN party role relationship prr ON child id   prr child id AND prr type    EMPLOYMENT  JOIN party role parent ON parent id   prr parent id   parent id JOIN party p2 ON parent party id   p2 id   And suddenly you have a very simple view of the data you want but on a highly flexible data model   7  Not sanitizing input  This is a huge one   Now I like PHP but if you don t know what you re doing it s really easy to create sites vulnerable to attack   Nothing sums it up better than the story of little Bobby Tables   Data provided by the user by way of URLs  form data and cookies should always be treated as hostile and sanitized   Make sure you re getting what you expect   8  Not using prepared statements  Prepared statements are when you compile a query minus the data used in inserts  updates and WHERE clauses and then supply that later   For example   SELECT   FROM users WHERE username    bob    vs  SELECT   FROM users WHERE username       or  SELECT   FROM users WHERE username    username   depending on your platform   I ve seen databases brought to their knees by doing this   Basically  each time any modern database encounters a new query it has to compile it   If it encounters a query it s seen before  you re giving the database the opportunity to cache the compiled query and the execution plan   By doing the query a lot you re giving the database the opportunity to figure that out and optimize accordingly  for example  by pinning the compiled query in memory    Using prepared statements will also give you meaningful statistics about how often certain queries are used   Prepared statements will also better protect you against SQL injection attacks   9  Not normalizing enough  Database normalization is basically the process of optimizing database design or how you organize your data into tables   Just this week I ran across some code where someone had imploded an array and inserted it into a single field in a database   Normalizing that would be to treat element of that array as a separate row in a child table  ie a one-to-many relationship    This also came up in Best method for storing a list of user IDs      I ve seen in other systems that the list is stored in a serialized PHP array    But lack of normalization comes in many forms   More    Normalization  How far is far enough  SQL by Design  Why You Need Database Normalization    10  Normalizing too much  This may seem like a contradiction to the previous point but normalization  like many things  is a tool   It is a means to an end and not an end in and of itself   I think many developers forget this and start treating a  means  as an  end    Unit testing is a prime example of this   I once worked on a system that had a huge hierarchy for clients that went something like   Licensee - gt   Dealer Group - gt  Company - gt  Practice - gt        such that you had to join about 11 tables together before you could get any meaningful data   It was a good example of normalization taken too far   More to the point  careful and considered denormalization can have huge performance benefits but you have to be really careful when doing this   More    Why too much Database Normalization can be a Bad Thing How far to take normalization in database design  When Not to Normalize your SQL Database Maybe Normalizing Isn t Normal The Mother of All Database Normalization Debates on Coding Horror   11  Using exclusive arcs  An exclusive arc is a common mistake where a table is created with two or more foreign keys where one and only one of them can be non-null   Big mistake   For one thing it becomes that much harder to maintain data integrity   After all  even with referential integrity  nothing is preventing two or more of these foreign keys from being set  complex check constraints notwithstanding    From A Practical Guide to Relational Database Design      We have strongly advised against exclusive arc construction wherever   possible  for the good reason that they can be awkward to write code   and pose more maintenance difficulties    12  Not doing performance analysis on queries at all  Pragmatism reigns supreme  particularly in the database world  If you re sticking to principles to the point that they ve become a dogma then you ve quite probably made mistakes   Take the example of the aggregate queries from above   The aggregate version might look  nice  but its performance is woeful   A performance comparison should ve ended the debate  but it didn t  but more to the point  spouting such ill-informed views in the first place is ignorant  even dangerous   13  Over-reliance on UNION ALL and particularly UNION constructs  A UNION in SQL terms merely concatenates congruent data sets  meaning they have the same type and number of columns   The difference between them is that UNION ALL is a simple concatenation and should be preferred wherever possible whereas a UNION will implicitly do a DISTINCT to remove duplicate tuples   UNIONs  like DISTINCT  have their place   There are valid applications   But if you find yourself doing a lot of them  particularly in subqueries  then you re probably doing something wrong   That might be a case of poor query construction or a poorly designed data model forcing you to do such things   UNIONs  particularly when used in joins or dependent subqueries  can cripple a database   Try to avoid them whenever possible   14  Using OR conditions in queries  This might seem harmless   After all  ANDs are OK   OR should be OK too right   Wrong   Basically an AND condition restricts the data set whereas an OR condition grows it but not in a way that lends itself to optimisation   Particularly when the different OR conditions might intersect thus forcing the optimizer to effectively to a DISTINCT operation on the result   Bad       WHERE a   2 OR a   5 OR a   11   Better       WHERE a IN  2  5  11    Now your SQL optimizer may effectively turn the first query into the second   But it might not   Just don t do it   15  Not designing their data model to lend itself to high-performing solutions  This is a hard point to quantify   It is typically observed by its effect   If you find yourself writing gnarly queries for relatively simple tasks or that queries for finding out relatively straightforward information are not efficient  then you probably have a poor data model   In some ways this point summarizes all the earlier ones but it s more of a cautionary tale that doing things like query optimisation is often done first when it should be done second   First and foremost you should ensure you have a good data model before trying to optimize the performance   As Knuth said      Premature optimization is the root of all evil   16  Incorrect use of Database Transactions  All data changes for a specific process should be atomic  I e  If the operation succeeds  it does so fully  If it fails  the data is left unchanged  - There should be no possibility of  half-done  changes   Ideally  the simplest way to achieve this is that the entire system design should strive to support all data changes through single INSERT UPDATE DELETE statements  In this case  no special transaction handling is needed  as your database engine should do so automatically   However  if any processes do require multiple statements be performed as a unit to keep the data in a consistent state  then appropriate Transaction Control is necessary    Begin a Transaction before the first statement  Commit the Transaction after the last statement  On any error  Rollback the Transaction  And very NB  Don t forget to skip abort all statements that follow after the error    Also recommended to pay careful attention to the subtelties of how your database connectivity layer  and database engine interact in this regard    17  Not understanding the  set-based  paradigm  The SQL language follows a specific paradigm suited to specific kinds of problems  Various vendor-specific extensions notwithstanding  the language struggles to deal with problems that are trivial in langues like Java  C   Delphi etc   This lack of understanding manifests itself in a few ways    Inappropriately imposing too much procedural or imperative logic on the databse  Inappropriate or excessive use of cursors  Especially when a single query would suffice  Incorrectly assuming that triggers fire once per row affected in multi-row updates    Determine clear division of responsibility  and strive to use the appropriate tool to solve each problem

User · Answer

Blaming the db engine when the query that ran sooo fast on your development machine blows up and choke once you throw some traffic at the application

User · Answer

1  Poor understanding of how to properly interact between Java and the database   2  Over parsing  improper or no reuse of SQL  3  Failing to use BIND variables  4  Implementing procedural logic in Java when SQL set logic in the database would have worked  better    5  Failing to do any reasonable performance or scalability testing prior to going into production  6  Using Crystal Reports and failing to set the schema name properly in the reports  7  Implementing SQL with Cartesian products due to ignorance of the execution plan  did you even look at the EXPLAIN PLAN

User · Answer

Thinking that they are DBAs and data modelers designers when they have no formal indoctrination of any kind in those areas   Thinking that their project doesn t require a DBA because that stuff is all easy trivial  Failure to properly discern between work that should be done in the database  and work that should be done in the app   Not validating backups  or not backing up   Embedding raw SQL in their code

User · Answer

Not doing the correct level of normalization   You want to make sure that data is not duplicated  and that you are splitting data into different as needed   You also need to make sure you are not following normalization too far as that will hurt performance

User · Answer

Well  I would have to say that the biggest mistake application developers make is not properly normalizing the database   As an application developer myself  I realize the importance of proper database structure  normalization  and maintenance  I have spent countless hours educating myself on database structure and administration  In my experience  whenever I start working with a different developer  I usually have to restructure the entire database and update the app to suit because it is usually malformed and defective   For example  I started working with a new project where the developer asked me to implement Facebook Connect on the site  I cracked open the database to see what I had to work with and saw that every little bit of information about any given user was crammed into one table  It took me six hours to write a script that would organize the table into four or five separate tables and another two to get the app to use those tables  Please  normalize your databases  It will make everything else less of a headache

User · Answer

Here is a link to video called     Classic Database Development Mistakes and five ways to overcome them    by Scott Walz

User · Answer

Over-use and or dependence on stored procedures     Some application developers see stored procedures as a direct extension of middle tier front end code   This appears to be a common trait in Microsoft stack developers   I m one  but I ve grown out of it  and produces many stored procedures that perform complex business logic and workflow processing   This is much better done elsewhere   Stored procedures are useful where it has actuallly been proven that some real technical factor necessitates their use  for example  performance and security  For example  keeping aggregation filtering of large data sets  close to the data      I recently had to help maintain and enhance a large Delphi desktop application of which 70  of the business logic and rules were implemented in 1400 SQL Server stored procedures  the remainder in UI event handlers    This was a nightmare  primarily due to the difficuly of introducing effective unit testing to TSQL  lack of encapsulation and poor tools  Debuggers  editors    Working with a Java team in the past I quickly found out that often the complete opposite holds in that environment   A Java Architect once told me   The database is for data  not code       These days I think it s a mistake to not consider stored procs at all  but they should be used sparingly  not by default  in situations where they provide useful benefits  see the other answers

User · Answer

Not using version control on the database schema Working directly against a live database  Not reading up and understanding more advanced database concepts  indexes  clustered indexes  constraints  materialized views  etc  Failing to test for scalability     test data of only 3 or 4 rows will never give you the real picture of real live performance

User · Answer

Not paying enough attention towards managing database connections in your application  Then you find out the application  the computer  the server  and the network is clogged

User · Answer

Very large transactions  inserting updating a lot of data and then reloading it  Basically this is down to not considering the multi-user environment the database works in  Overuse of functions  specifically as results in selects and in where clauses which causes the function to be called over and over again for the results  This  I think  fits under the general case of them trying to work in the procedural fashion they re more used to rather than use SQL to its full advantage

User · Answer

Not executing a corresponding SELECT query before running the DELETE query  particularly on production databases

User · Answer

Not using parameterized queries   They re pretty handy in stopping SQL Injection    This is a specific example of not sanitizing input data  mentioned in another answer

User · Answer

For SQL-based databases    Not taking advantage of CLUSTERED INDEXES or choosing the wrong column s  to CLUSTER  Not using a SERIAL  autonumber  datatype as a PRIMARY KEY to join to a FOREIGN KEY  INT  in a parent child table relationship  Not UPDATING STATISTICS on a table when many records have been INSERTED or DELETED  Not reorganizing  i e  unloading  droping  re-creating  loading and re-indexing  tables when many rows have been inserted or deleted  some engines physically keep deleted rows in a table with a delete flag   Not taking advantage of FRAGMENT ON EXPRESSION  if supported  on large tables which have high transaction rates  Choosing the wrong datatype for a column  Not choosing a proper column name  Not adding new columns at the end of the table  Not creating proper indexes to support frequently used queries  creating indexes on columns with few possible values and creating unnecessary indexes     more to be added

User · Answer

Using an ORM to do bulk updates Selecting more data than needed  Again  typically done when using an ORM Firing sqls in a loop  Not having good test data and noticing performance degradation only on live data

User · Answer

Dismissing an ORM like Hibernate out of hand  for reasons like  it s too magical  or  not on my database   Relying too heavily on an ORM like Hibernate and trying to shoehorn it in where it isn t appropriate

User · Answer

Not using indexes

User · Answer

Poor Performance Caused by Correlated Subqueries  Most of the time you want to avoid correlated subqueries   A subquery is correlated if  within the subquery  there is a reference to a column from the outer query   When this happens  the subquery is executed at least once for every row returned and could be executed more times if other conditions are applied after the condition containing the correlated subquery is applied   Forgive the contrived example and the Oracle syntax  but let s say you wanted to find all the employees that have been hired in any of your stores since the last time the store did less than  10 000 of sales in a day   select e first name  e last name from employee e where e start date  gt            select max ds transaction date           from daily sales ds          where ds store id   e store id and                ds total  lt  10000    The subquery in this example is correlated to the outer query by the store id and would be executed for every employee in your system   One way that this query could be optimized is to move the subquery to an inline-view   select e first name  e last name from employee e        select ds store id               max s transaction date  transaction date       from daily sales ds       where ds total  lt  10000       group by s store id  dsx where e store id   dsx store id and       e start date  gt  dsx transaction date   In this example  the query in the from clause is now an inline-view  again some Oracle specific syntax  and is only executed once   Depending on your data model  this query will probably execute much faster   It would perform better than the first query as the number of employees grew   The first query could actually perform better if there were few employees and many stores  and perhaps many of stores had no employees  and the daily sales table was indexed on store id   This is not a likely scenario but shows how a correlated query could possibly perform better than an alternative   I ve seen junior developers correlate subqueries many times and it usually has had a severe impact on performance   However  when removing a correlated subquery be sure to look at the explain plan before and after to make sure you are not making the performance worse

User · Answer

I hate it when developers use nested select statements or even functions the return the result of a select statement inside the  SELECT  portion of a query   I m actually surprised I don t see this anywhere else here  perhaps I overlooked it  although  adam has a similar issue indicated   Example   SELECT      SELECT TOP 1 SomeValue FROM SomeTable WHERE SomeDate   c Date ORDER BY SomeValue desc  As FirstVal       SELECT OtherValue FROM SomeOtherTable WHERE SomeOtherCriteria   c Criteria  As SecondVal FROM     MyTable c   In this scenario  if MyTable returns 10000 rows the result is as if the query just ran 20001 queries  since it had to run the initial query plus query each of the other tables once for each line of result   Developers can get away with this working in a development environment where they are only returning a few rows of data and the sub tables usually only have a small amount of data  but in a production environment  this kind of query can become exponentially costly as more data is added to the tables   A better  not necessarily perfect  example would be something like   SELECT      s SomeValue As FirstVal      o OtherValue As SecondVal FROM     MyTable c     LEFT JOIN           SELECT SomeDate  MAX SomeValue  as SomeValue         FROM SomeTable          GROUP BY SomeDate        s ON c Date   s SomeDate     LEFT JOIN SomeOtherTable o ON c Criteria   o SomeOtherCriteria   This allows database optimizers to shuffle the data together  rather than requery on each record from the main table and I usually find when I have to fix code where this problem has been created  I usually end up increasing the speed of queries by 100  or more while simultaneously reducing CPU and memory usage

User · Answer

15 - Using some crazy construct and application logic instead of a simple COALESCE

User · Answer

1 - Unnecessarily using a function on a value in a where clause with the result of that index not being used   Example   where to char someDate  YYYYMMDD   between  fromDate and  toDate   instead of  where someDate  gt   to date  fromDate  YYYYMMDD   and someDate  lt  to date  toDate  YYYYMMDD   1   And to a lesser extent  Not adding functional indexes to those values that need them     2 - Not adding check constraints to ensure the validity of the data  Constraints can be used by the query optimizer  and they REALLY help to ensure that you can trust your invariants  There s just no reason not to use them   3 - Adding unnormalized columns to tables out of pure laziness or time pressure  Things are usually not designed this way  but evolve into this  The end result  without fail  is a ton of work trying to clean up the mess when you re bitten by the lost data integrity in future evolutions   Think of this  a table without data is very cheap to redesign  A table with a couple of millions records with no integrity    not so cheap to redesign  Thus  doing the correct design when creating the column or table is amortized in spades   4 - not so much about the database per se but indeed annoying  Not caring about the code quality of SQL  The fact that your SQL is expressed in text does not make it OK to hide the logic in heaps of string manipulation algorithms  It is perfectly possible to write SQL in text in a manner that is actually readable by your fellow programmer

User · Answer

I d like to add  Favoring  Elegant  code over highly performing code  The code that works best against databases is often ugly to the application developer s eye    Believing that nonsense about premature optimization  Databases must consider performance in the original design and in any subsequent development  Performance is 50  of database design  40  is data integrity and the last 10  is security  in my opinion  Databases which are not built from the bottom up to perform will perform badly once real users and real traffic are placed against the database  Premature optimization doesn t mean no optimization  It doesn t mean you should write code that will almost always perform badly because you find it easier  cursors for example which should never be allowed in a production database unless all else has failed   It means you don t need to look at squeezing out that last little bit of performance until you need to  A lot is known about what will perform better on databases  to ignore this in design and development is short-sighted at best

User · Answer

The most common mistake I ve seen in twenty years  not planning ahead   Many developers will create a database  and tables  and then continually modify and expand the tables as they build out the applications   The end result is often a mess and inefficient and difficult to clean up or simplify later on

User · Answer

If you are using replication  MySQL   following functions are unsafe unless you are using  row-based replication   USER    CURRENT USER    or CURRENT USER   UUID    VERSION    LOAD FILE    and RAND     See  http   dev mysql com doc refman 5 1 en replication-features-functions html

User · Answer

Not understanding how a DBMS works under the hood    You cannot properly drive a stick without understanding how a clutch works  And you cannot understand how to use a Database without understanding that you are really just writing to a file on your hard disk    Specifically     Do you know what a Clustered Index is  Did you think about it when you designed your schema   Do you know how to use indexes properly  How to reuse an index  Do you know what a Covering Index is   So great  you have indexes  How big is 1 row in your index  How big will the index be when you have a lot of data  Will that fit easily into memory  If it won t it s useless as an index   Have you ever used EXPLAIN in MySQL  Great  Now be honest with yourself  Did you understand even half of what you saw  No  you probably didn t  Fix that   Do you understand the Query Cache  Do you know what makes a query un-cachable   Are you using MyISAM  If you NEED full text search  MyISAM s is crap anyway  Use Sphinx  Then switch to Inno

User · Answer

Using Access instead of a  real  database   There are plenty of great small and even free databases like SQL Express  MySQL  and SQLite that will work and scale much better   Apps often need to scale in unexpected ways

User · Answer

Not having an understanding of the databases concurrency model and how this affects development   It s easy to add indexes and tweak queries after the fact  However applications designed without proper consideration for hotspots  resource contention  and correct operation  Assuming what you just read is still valid   can require significant changes within the database and application tier to correct later

User · Answer

Using Excel for storing  huge amounts of  data   I have seen companies holding thousands of rows and using multiple worksheets  due to the row limit of 65535 on previous versions of Excel      Excel is well suited for reports  data presentation and other tasks  but should not be treated as a database

User · Answer

Not taking a backup before fixing some issue inside production database  Using DDL commands on stored objects like tables  views  in stored procedures  Fear of using stored proc or fear of using ORM queries wherever the one is more efficient appropriate to use  Ignoring the use of a database profiler  which can tell you exactly what your ORM query is being converted into finally and hence verify the logic or even for debugging when not using ORM

[database] Database development mistakes made by application developers

Examples related to database

Examples related to database-design