MyISAM vs InnoDB….??

Storage Engines are the programs that are integrated with a MYSQL database management to manage the data tables. MYSQL supports different storage engines that handles different tables.

Selection of Storage Engine:

Selection of storage engine, depends on users table type and its purpose. Each has their own advantages and disadvantages. Also, possible to select engine at the time of creating tables.
While selecting storage engine, the factors that can affect your selection are:

• Transaction and concurrency
• Backups
• Special features

You should use INNODB for your tables unless you have a compelling need to use a different engine!! (To get High Performance)

By- Peter Zaitsev

Some of important Storage Engines are :
MyISAM, INNODB, MERGE, MEMORY, etc.

The major thing that one should know what are the difference between InnoDB and MyISAM???

MyISAM

(+)

• Designed with a thinking that database are frequently read not updated.
• Simple to understand and implement.
• Has full text searching capability.
• Good for frequent read operations(when several computations are executing simultaneously)
• Faster reads and writes for small to medium sized tables.
• Lock tables while updating.
• Count(*) queries are fast

(-)

• Non-transactional. Data loss issues during crashes.
• Table level locking
• Insert and update queries are scaleable, concurrency issues

InnoDB

(+)

• Designed with a thinking that database are frequently updated not read.
• Transaction-safe
• Data-integrity is maintained throughout the entire query process.
• Clustering by primary key for faster lookups and ordering.
• Transactional, crash-safe, better online backup capability.
• Allow multiple updates on single table
• Locks only row, not table.

(-)

• Slower writes(insert, update queries)
• Slower BLOB handling
• Count(*) queries require full table scan.

Simple comparison chart

	MyISAM	InnoDB
Required full text Search	Yes	5.6+
Require Transactions		Yes
Frequent select queries	Yes
Frequent insert,update,delete		Yes
Row Locking (multi processing on single table)		Yes
Relational base design		Yes

 

Summary:

Frequent reading, almost no writing => MyISAM
Full text search in MySQL <= 5.5 => MyISAM

In all other circumstances, InnoDB is usually the best way to go and INNODB can now use Sphinx for Full Text Searching.

Reference:

http://www.slideshare.net/gauravpaliwal/mysql-engines
http://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

Enjoy Coding! 🙂

 

FULL-TEXT search using MYSQL

MySQL provides a wonderful way (Full-text Search) of implementing a little search engine in your website. All you have to do is to have MySQL 4.x and above.

Why FULLTEXT ??

MySQL allow text searching by using the LIKE and regular expression. But, these methods have some limitations like:

• Performance: MySQL need to scan whole table to find the exact text based on pattern in the LIKE  or pattern in the regular expressions.
• Flexible search: It is difficult to have flexible search query e.g., to find product whose description contains car but not classic.
• Relevance ranking: No way to specify which row in the result set that is more relevant

Features of MySQL full-text search:

• Native SQL-like interface: you use SQL-like statement to use the full-text search.
• Searches are not case sensitive
• Fully dynamic index: MySQL automatically index the text column whenever the data changes. You don’t need to run the index periodically.
• It does not take much memory to store the index.
• It is fast to search based on complex search query.

Get Started

Before we start, it’s important to know about these keywords:

MATCH

The MATCH function is used to specify the column names that identify your FULLTEXT collection. The column list inside the MATCH function must exactly match that of the FULLTEXT index definition, unless your search in boolean mode (see below).

AGAINST

The AGAINST function is where your full text search query goes. Besides the default natural language search mode, you can perform boolean mode searches, and use query expansion.

Lets start with some sample table and query:

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);
// Insert some data into table

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

Here two types of search are possible:

• Natural Language Full-Text Searches
• Boolean Full-Text Searches

Natural Language Full-Text Searches

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');

This query will fetch all the records which has database keyword.

Boolean Full-Text Searches

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

In above query you see that I have added IN BOOLEAN MODE in against(). This query will fetch all the records which has MySQL keyword but not YourSQL keyword. (+ and – that we have specified before the keywords)

+ stands for AND
- stands for NOT
[no operator] implies OR

Remember:

Not all storage engines support full-text searching. In MySQL, only MyISAM and InnoDB storage engines support full-text search. The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow.

Enjoy Coding! 🙂

Reference: http://viralpatel.net/blogs/full-text-search-using-mysql-full-text-search-capabilities/

Identify your slow Running QUERY?

We all know, it not good to keep queries which take long time to run and fetch result. But, one point also comes in mind what is the quick way to identify the Slow Running Query.

One of the good thing is MYSQL provide one way from which we can easily identify the slow running queries, and also store that slow running query in log.

“MYSQL doesn’t store SLOW running queries by default.”

To Enable Log for Slow Queries:

Edit my.cnf (configuration file) of mysql and uncomment these line under [mysqld] block:

long_query_time         = 1  // in seconds
log_slow_queries       = /var/log/mysql/mysql-slow.log

The default long_query_time is 0, but we’ve set it to 1 here to make MySQL log all queries that take longer than 1 second to execute.

Feel free to change this value, as well as the location for the log file. You can then use the
mysqldumpslow command-line tool, included with MySQL, to get a summary of the slow-query-log file.

When you’re done, you need to restart the MySQL server for the changes in the my.cnf file to take effect

/etc/init.d/mysql restart
OR
sudo service mysql restart

And its done!

Enjoy Coding! 🙂

 

Does MYSQL supports PaTTerN ?????

“Regular Expression” often called as ‘pattern’ is an expression used to specify a set of strings required for a particular purpose. Many applications and programming languages have their own implementation of regular expressions, often with slight and sometimes with significant differences from other implementations.

Similarly, MYSQL also supports Regular  Expression. You must have seen MYSQL pattern matching with LIKE. But, MYSQL also supports another type of pattern matching operation based on regular expression i.e. REGEXP operator.

May be Question come to your mind. If MYSQL provide LIKE Operator for Pattern matching. Then, why I need REGEXP Operator. So, here your answer.

Why use REGEXP?
Because it has no boundaries, you can write your own patterns. As you are not only limited to search for a string based on any fixed pattern like MYSQL LIKE operator.

MYSQL provides three types of pattern opeartors:

• REGEXP (Pattern matching using regular expressions)
• NOT_REGEXP (Negation of REGEXP)
• RLIKE (Synonym for REGEXP)

Syntax of the Pattern operator:

SELECT column_name
 FROM table_name
 WHERE column_name REGEXP pattern,
 column_name NOT_REGEXP pattern;

Here, listed some patterns, can be used with REGEX Operator

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
|	Separates alternatives
[…]	Any character listed between the square brackets
[^…]	Any character not listed between the square brackets
p1|p2|p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{n}	n instances of preceding element
{m,n} {n, }	m through n instances of preceding elementmatch n or more times
[:space:]	For Whitespace
[:upper:]	For upper case Letters
[:alpha:]	For Letters

MYSQL Regular Expression Example:
Consider we want to find out the names of students starts with N, B or T.

SELECT name, class, marks, age
 FROM students
 WHERE name REGEXP ‘^(N|B|T)’;

Next, let’s consider we need to find out names of students starting with vowel and end with ‘r’

SELECT name, class, marks, age
 FROM students
 WHERE name REGEXP ‘^[aeiou] | r$’;

I hope after reading you will get clear idea about Regular Expression in MYSQL.
Enjoy Coding! 🙂

Reference: http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

ApaChE Tips

We all know while development, sometimes we need to setup different projects in our system. For which we have to do settings (like virtual host, host setup,…) to run it in our local environment.

Here, I am sharing some tips for Apache that might be helpful for doing setup of different projects.

Setup a Virtual Domain

NameVirtualHost *
<VirtualHost *>
DocumentRoot /web/example.com/www
ServerName www.example.com
ServerAlias example.com CustomLog /web/example.com/logs/access.log combined ErrorLog /web/example.com/logs/error.log
</VirtualHost> 

Include another conf file

Include /etc/apache/virtual-hosts/*.conf

Hide Apache Version Info

ServerSignature Off
ServerTokens Prod

Only allow Access from a specific IP

Order Deny,Allow
Deny from all
Allow from 127.0.0.1

Only allow access from your subnet

Order Deny,Allow
Deny from all
Allow from 176.16.0.0/16

Add a directory index

DirectoryIndex index.cfm index.cfm

Turn OFF directory browsing

Options -Indexes

Turn ON directory browsing

<Location /images>
  Options +Indexes
</Location>

Enjoy Coding! 🙂