MySql Database Design Best Practices

1. Optimize Your Queries For the Query Cache

2. EXPLAIN Your SELECT Queries

3. LIMIT 1 When Getting a Unique Row

4. Index the Search Fields

5. Do Not ORDER BY RAND()

6. Avoid SELECT *

7. Almost Always Have an id Field

8. Use ENUM over VARCHAR

9.Get Suggestions with PROCEDURE ANALYSE()

10.Use NOT NULL If You Can

11.Prepared Statements

12.Fixed-length (Static) Tables are Faster

13.Split the Big DELETE or INSERT Queries

14.Smaller Columns Are Faster

15.Choose the Right Storage Engine

a)MyISAM Storage Engine

b)InnoDB Storage Engine1. Optimize Your Queries For the Query Cache

2. EXPLAIN Your SELECT Queries

3. LIMIT 1 When Getting a Unique Row

4. Index the Search Fields

5. Do Not ORDER BY RAND()

6. Avoid SELECT *

7. Almost Always Have an id Field

8. Use ENUM over VARCHAR

9.Get Suggestions with PROCEDURE ANALYSE()

10.Use NOT NULL If You Can

11.Prepared Statements

12.Fixed-length (Static) Tables are Faster

13.Split the Big DELETE or INSERT Queries

14.Smaller Columns Are Faster

15.Choose the Right Storage Engine

a)MyISAM Storage Engine

b)InnoDB Storage Engine

16. Use an Object Relational Mapper

17. Use well defined and consistent names for tables and columns

18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.

19. Don’t use unnecessary prefixes or suffixes for table names .

20. Keep passwords as encrypted for security. Decrypt them in application when required.

21. Provide authentication for database access. Don’t give admin role to each user.

 22.Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis ...) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.

16. Use an Object Relational Mapper

17. Use well defined and consistent names for tables and columns

18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.

19. Don’t use unnecessary prefixes or suffixes for table names .

20. Keep passwords as encrypted for security. Decrypt them in application when required.

21. Provide authentication for database access. Don’t give admin role to each user.

 22.Use an ORM (object relational mapping) framework  if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.

How to Define an Auto Increment Primary Key in Oracle

 Create the table

 create table honnikery (
      id number primary key,
      name varchar2(100)
    );

Create sequence
 create sequence honni_id_seq;

create a trigger that uses the sequence to populate the primary key
create trigger trg_honni_id
      before insert on honnikery
      for each row
    begin
      select honni_id_seq.nextval
        into :new.id
        from dual;
    end;



insert into honnikery( name ) values ('honnikery Prabhakar');
insert into honnikery( name ) values ('Biswa');

How does one get the time difference between two date columns SQL

How does one get the time difference between two date columns
 in hours , minutes and seconds



SELECT floor((D1-D2)*24)
      || ' HOURS ' ||
          mod(floor((D1-D2)*24*60),60)
        || ' MINUTES ' ||
        mod(floor((D1-D2)*24*60*60),60)
         || ' SECS ' time_difference
    FROM honnikery;



TIME_DIFF
--------------------------------------------------------------------------------
1 HOURS 0 MINUTES 0 SECS
1 HOURS 10 MINUTES 0 SECS
10 HOURS 1 MINUTES 10 SECS

Finding tables without Primary or Unique Keys (Mysql)

Finding tables without Primary or Unique Keys:


SELECT t.TABLE_SCHEMA,t.TABLE_NAME,ENGINE
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
AND t.TABLE_SCHEMA NOT IN ('performance_schema','information_schema','mysql')
GROUP BY t.TABLE_SCHEMA,t.TABLE_NAME
HAVING sum(if(column_key in ('PRI','UNI'), 1,0))=0;





Finding Foreign key constraints:


SELECT referenced_table_name parent, table_name child, constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
ORDER BY referenced_table_name;

MySQL Error Handling

Three type of Handler_Action:

CONTINUE
EXIT
UNDO

Type of Condition Value:

mysql_error_code
sqlstate_value
SQLWarning
SQLException
NotFound



GET DIAGNOSTICS, you can get all of the error information, and you should, if not already.

If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

GET DIAGNOSTICS CONDITION 1
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

MySQL error code: 1175 during UPDATE in MySQL Workbench

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.000 sec

Try disabling that using these steps -

Edit > Preferences > Sql Editor > uncheck the "Safe Updates"

Note - try reconnecting the server (Query > Reconnect to Server) and than run your query again.

MySQL date and time functions

MySQL date and time functions
ADDDATE()
ADDTIME()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_ TIMESTAMP()
CURTIME()
DATE_ADD()
DATE_FORMAT()
DATE_SUB()
DATE()
DATEDIFF()
DAY()
DAYNAME()
DAY OF MONTH()
DAY OF WEEK()
DAY OF YEAR()
EXTRACT()
FROM_DAYS()
FROM_UNIXTIME()
GET_FORMAT()
HOUR()
LAST_DAY()
LOCALTIME()
LOCALTIMESTAMP()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
NOW()
PERIOD_ADD()
PERIOD_DIFF()
QUARTER()
SEC_TO_TIME()
SECOND()
STR_TO_DATE()
SUBDATE()
SUBTIME()
SYSDATE()
TIME_FORMAT()
TIME_TO_SEC()
TIME()
TIMEDIFF()
TIMESTAMP()
TIMESTAMPADD()
TIMESTAMPDIFF()
TO_DAYS()
UNIX_TIMESTAMP()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
WEEK()
WEEKDAY()
WEEK OF YEAR()
YEAR()
YEARWEEK()