Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Friday, March 29, 2013

What are the difference between DDL, DML DCL and TCL commands?

Following difference between DDL, DML DCL and  TCL commands



Data definition language(DDl)
DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

  1. CREATE - to create objects in the database
  2. ALTER - alters the structure of the database
  3. DROP - delete objects from the database
  4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  5. COMMENT - add comments to the data dictionary
  6. RENAME - Rename an object/Table

Data Manipulation Language(DML)
DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can’t be seen by anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it.

  1. SELECT - retrieve data from the a database
  2. INSERT - insert data into a table
  3. UPDATE - updates existing data within a table
  4. DELETE - deletes all records from a table, the space for the records remain
  5. MERGE - UPSERT operation (insert or update)
  6. CALL - call a PL/SQL or Java subprogram
  7. EXPLAIN PLAN - explain access path to data
  8. LOCK TABLE - control concurrency
Data Control Language (DCL)
DCL is Stand for  Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

  1. GRANT - gives user's access privileges to database
  2. REVOKE - withdraw access privileges given with the GRANT command

Transactional Control Language(TCL)
TCL is Stand for Transactional Control Language. It is used to manage different transactions occurring within a database.
  1. COMMIT - save work done
  2. SAVEPOINT - identify a point in a transaction to which you can later roll back
  3. ROLLBACK - restore database to original since the last COMMIT
  4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 
 ROLLBACK is used for revoking the transactions until last commit.
COMMIT is used for commiting the transactions to the database.
Once we commit we cannot rollback. Once we rollback we cannot commit.
Commit and Rollback are generally used to commit or revoke the transactions that are with regard to DML commands.



NOTE:
DML commands can't be rollback when a DDL command is executed immediately after a DML. DDL after DML means "auto commit". The changes will return on disk not on the buffer. If the changes return on the buffer it is possible to rollback not from the disk


Tuesday, March 19, 2013

Speed Up Mysql Queries

You have Given  Tips Using Speed Up  Mysql Queries/Database . Following point remember using mysql query.

  • Use persistent connections to the database to avoid connection overhead.
  • Check all tables have PRIMARY KEYs on Columns/Fields with high cardinality (many rows match the key value). Well,`gender` column has low cardinality (selectivity), unique user id column has high one and is a good candidate to become a primary key.
  • All references between different tables should usually be done with indices (which also means they must have identical data types so that joins based on the corresponding Columns/Fields will be faster). Also check that fields that you often need to search in (appear frequently in WHERE, ORDER BY or GROUP BY clauses) have indices, but don’t add too many: the worst thing you can do is to add an index on every column of a table (I haven’t seen a table with more than 5 indices for a table, even 20-30 Columns/Fields big). If you never refer to a column in comparisons, there’s no need to index it.
  • Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.
  • Use less RAM per row by declaring Columns/Fields only as large as they need to be to hold the values stored in them.
  • Use leftmost index prefix — in MySQL you can define index on several Columns/Fields so that left part of that index can be used a separate one so that you need less indices.
  • When your index consists of many Columns/Fields, why not to create a hash column which is short, reasonably unique, and indexed? Then your query will look like:
  • Consider running ANALYZE TABLE (or myisamchk --analyze from command line) on a table after it has been loaded with data to help MySQL better optimize queries.
  • Use CHAR type when possible (instead of VARCHAR, BLOB or TEXT) — when values of a column have constant length: MD5-hash (32 symbols), ICAO or IATA airport code (4 and 3 symbols), BIC bank code (3 symbols), etc. Data in CHAR Columns/Fields can be found faster rather than in variable length data types Columns/Fields.
  • Don’t split a table if you just have too many Columns/Fields. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row.
  • A column must be declared as NOT NULL if it really is — thus you speed up table traversing a bit.
  • If you usually retrieve rows in the same order like expr1, expr2, ..., make ALTER TABLE ... ORDER BY expr1, expr2, ... to optimize the table.
  • Don’t use PHP loop to fetch rows from database one by one just because you can — use IN instead,
  • Example: SELECT * FROM `tableNAME` WHERE `id` IN (27,7,17,1,3);
  • Don’t use REPLACE (which is DELETE+INSERT and wastes ids): use INSERT … ON DUPLICATE KEY UPDATE instead (i.e. it’s INSERT + UPDATE if conflict takes place). The same technique can be used when you need first make a SELECT to find out if data is already in database, and then run either INSERT or UPDATE. Why to choose yourself — rely on database side.
  • Tune MySQL caching: allocate enough memory for the buffer (e.g. SET GLOBAL query_cache_size = 1000000) and define query_cache_min_res_unit depending on average query resultset size.
  • Use column default value, and insert only those values that differs from the default. This reduces the query parsing time.
  • Use INSERT DELAYED or INSERT LOW_PRIORITY (for MyISAM) to write to your change log table. Also, if it’s MyISAM, you can add DELAY_KEY_WRITE=1 option — this makes index updates faster because they are not flushed to disk until the table is closed.
  • Think of storing users sessions data (or any other non-critical data) in MEMORY table — it’s very fast.
  • For your web application, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database.
  • If you have to store big amounts of textual data, consider using BLOB column to contain compressed data (MySQL’s COMPRESS() seems to be slow, so gzipping at PHP side may help) and decompressing the contents at application server side. Anyway, it must be benchmarked.
  • If you often need to calculate COUNT or SUM based on information from a lot of rows (articles rating, poll votes, user registrations count, etc.), it makes sense to create a separate table and update the counter in real time, which is much faster. If you need to collect statistics from huge log tables, take advantage of using a summary table instead of scanning the entire log table every time.
  • Divide complex queries into several simpler ones — they have more chances to be cached, so will be quicker.
  • Group several similar INSERTs in one long INSERT with multiple VALUES lists to insert several rows at a time: quiry will be quicker due to fact that connection + sending + parsing a query takes 5-7 times of actual data insertion (depending on row size). If that is not possible, use START TRANSACTION and COMMIT, if your database is InnoDB, otherwise use LOCK TABLES — this benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed; in this case unlock your tables each 1000 rows or so to allow other threads access to the table.
  • When loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times faster.
  • Log slow queries on your dev/beta environment and investigate them. This way you can catch queries which execution time is high, those that don’t use indexes, and also — slow administrative statements (like OPTIMIZE TABLE and ANALYZE TABLE)
  • Tune your database server parameters: for example, increase buffers size.
  • If you have lots of DELETEs in your application, or updates of dynamic format rows (if you have VARCHAR, BLOB or TEXT column, the row has dynamic format) of your MyISAM table to a longer total length (which may split the row), schedule running OPTIMIZE TABLE query every weekend by crond. Thus you make the defragmentation, which means more speed of queries. If you don’t use replication, add LOCAL keyword to make it faster.
  • Don’t use ORDER BY RAND() to fetch several random rows. Fetch 10-20 entries (last by time added or ID) and make array_random() on PHP side. There are also other solutions.
  • Consider avoiding using of HAVING clause — it’s rather slow.
  • In most cases, a DISTINCT clause can be considered as a special case of GROUP BY; so the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Also, if you use DISTINCT, try to use LIMIT (MySQL stops as soon as it finds row_count unique rows) and avoid ORDER BY (it requires a temporary table in many cases).
  • When I read “Building scalable web sites”, I found that it worth sometimes to de-normalise some tables (Flickr does this), i.e. duplicate some data in several tables to avoid JOINs which are expensive. You can support data integrity with foreign keys or triggers.
  • If you want to test a specific MySQL function or expression, use BENCHMARK function to do that.

Saturday, March 9, 2013

Calculating Age using MySQL

We want to  Calculating Age Current date from Date of Birth in MySQL.Following Given many option find out  age.



SELECT NOW()  - Returns the current date and time
Output- 2013-03-09 14:16:44
SELECT CURDATE()  - Returns the current date
Output- 2013-03-09


1. SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT('1981-10-25', '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT('1981-10-25', '00-%m-%d'))
AS age
output- 31

2. SELECT YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS('1981-10-25') DAY)) AS age
output- 31

3. SELECT ROUND(DATEDIFF(CURDATE(),'1981-10-25')/365) AS age 
output- 31

MySQL Date Functions

DATE_SUB() - The DATE_SUB() function subtracts a specified time interval from a date.
CURTIME() - Returns the current time
DATE() - Extracts the date part of a date or date/time expression
EXTRACT() -Returns a single part of a date/time
DATE_ADD()- Adds a specified time interval to a date
DATEDIFF()- Returns the number of days between two dates
DATE_FORMAT()- Displays date/time data in different formats
TO_DAYS() - Return the date argument converted to days

More function see it

Tuesday, February 26, 2013

Advantages and disadvantages of MyISAM and InnoDB


The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,


Following InnoDB vs. MyISAM – A Comparison of Pros and Cons
  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.
In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.


Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.
The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.



How to change or convert MyISAM to InnoDB or vice versa?



  • MyISAM tables yield better performance results in selecting data records
  • InnoDB tables give better performance results in inserting and updating data records
To change a MyISAM table to an InnoDB table:
ALTER TABLE table_name ENGINE = InnoDB;
To convert an InnoDB table to a MyISAM table:
ALTER TABLE table_name ENGINE = MyISAM;