Indexing in MySql

Hello Friends,

Here i am with another new concept in MySql Index.


Do you understand what an index is and how it works?

An index is an ordering of (part of) the columns in a row to improve access speed to a specific row or subset of rows. As others have said, the purpose is performance.

There are two ways to implement an index:
(1) logically arrange the rows themselves according to the sequence defined by the index. This is a "clustered" index. Since the rows can only be arranged in one sequence, there can only be one "clustered" index.
(2) leave the rows alone, but create a separate structure made up of only the indexed columns and a reference to the actual row. Order these according to the sequence of the index. This is a "non-clustered" index. (Also known as "alternate index" or "secondary index".) This index has no bearing on the way the data rows are organized.

When you access data through a "non-clustered" index, the system does two record lookups: first, it searches the index to find the index columns that meet the predicate criteria. When it finds one, it uses the reference to go to the table itself and retrieve the actual row.

When you access data through a "clustered" index, the system does one record lookup: it searches the index to find the index columns that meet the predicate criteria. When it finds one, the rest of the columns (the actual row) are stored with the index.

So a clustered index is faster than a non-clustered index, but you can only have one. A non-clustered index is faster than reading the whole table if you are looking for a very small percentage of the rows. If you are looking for a large portion of the rows, it may be faster to skip the index and just read the whole table.

All of this is in the physical design of the table.

The primary key, on the other hand, is in the logical design of the table. The primary key is unique -- there can only be one row with a given primary key value. It identifies exactly one row.

In most databases, it makes sense to implement the primary key as a clustered index. Not all databases do this -- some implement the primary key as a non-clustered index, separate from the data rows.

Finally -- in some databases, a uniqueness constraint is implemented using a non-clustered index. This special-purpose index is not for performance reasons; rather, it is a physical mechanism for enforcing uniqueness.

In addition to the above post Here i am adding a URL which would guide you to the best knowledge in INDEXING Concepts in MySql. I went through the below link and it was really useful.

http://sqlserverpedia.com/wiki/Indexing_Strategies

MySql Basics

Hi friends.,

I my previous chapters i have discussed about what Mysql is and its invention. In this chapter i would tell you from where to download mysql and some links related to it. i shall also give you the exact syntax that must be used to execute Mysql commands.

Frankly, Mysql commands are very easy to understand and work. Let me start of the introduction to mysql downloads.

Use this link to download MySql.
http://dev.mysql.com/downloads/mysql/

once you have installed MySql in you PC

To start or stop MySQL use the following commands:

• scripts/mysql.server stop
• scripts/mysql.server start

INTRODUCTION TO DATABASE DESIGN:

General Database and Table Properties:

Every MySQL server has a data directory under which it manages the contents of databases and
tables. The server represents these using directories and files under the data directory as follows:

MySQL associates each database with a directory under the data directory. (This means
that the data directory is the parent of all database directories.) A database directory has
the same name as the database that it represents. For example, a database named world
corresponds to a directory named world under the data directory. MySQL uses the
database directory to manage the components of the database—that is, its tables and
indexes.

A database may be empty, or have one or more tables. Databases cannot be
nested; one database cannot contain another.

Each table in a database consists of rows and columns. A table can be empty (it can have
zero rows of data), but it must have at least one column. A table may also be indexed to
improve query performance.

Every table is associated with a format file in the database
directory that contains the definition, or structure, of the table. The format filename is the
same as the table name, plus an .frm suffix.

For example, the format file for a table named
Country in the world database is named Country.frm and is located in the world
directory under the server's data directory. Depending on the table type, the storage
engine for a table might create additional files for the table.

Storage Engines and Table Types:

1.MyISAM Tables

The MyISAM storage engine manages tables that have the following characteristics:

Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD
data file and an .MYI index file. All these files are located in the database directory.

MyISAM has the most flexible AUTO_INCREMENT column handling of all the table
types.

MyISAM tables can be used to set up MERGE tables.

MyISAM tables can be converted into fast, compressed, read-only tables.

MyISAM supports FULLTEXT searching.


2.InnoDB Tables

1.The InnoDB storage engine manages tables that have the following characteristics:
InnoDB table is represented on disk by an .frm format file in the database directory,

2. InnoDB supports transactions (using the SQL COMMIT and ROLLBACK statements)
with full ACID compliance.

3. InnoDB provides auto-recovery after a crash of the MySQL server or the host where the
server runs.

4. InnoDB supports foreign keys and referential integrity, including cascaded deletes and
updates.

5. MySQL manages query contention for InnoDB tables using multi-versioning and rowlevel
locking.

3. MERGE Tables

1. A MERGE table is a collection of identically structured MyISAM tables. Each MERGE
table is represented on disk by an .frm format file and an .MRG file that lists the names of
the constituent MyISAM files. Both files are located in the database directory.

2. Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it
consists.

3. A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.


4.BDB (Berkeley DB) Tables

1.Each BDB table is represented on disk by an .frm format file and a .db file that stores data
and index information. Both files are located in the database directory.

2 BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with
full ACID compliance.

3.BDB provides auto-recovery after a crash of the MySQL server or the host where the
server runs.

4. MySQL manages query contention for BDB tables using page-level locking.

5.HEAP (MEMORY) Tables

1. Each HEAP table is represented on disk by an .frm format file in the database directory.
Table data and indexes are stored in memory.

2. In-memory storage results in very fast performance.

3.HEAP table contents do not survive a restart of the server. The table structure itself
survives, but the table contains zero data rows after a restart.

4. HEAP tables use up memory, so they should not be used for large tables.

5 MySQL manages query contention for HEAP tables using table-level locking. Deadlock
cannot occur.


Identifier Syntax

When you write SQL statements, you use names to refer to databases and tables as well as to
elements of tables such as columns and (sometimes) indexes. It's also possible to create aliases,
which act as synonyms for table and column names. All of these types of names are known as
identifiers.

Parameters to create Identifiers


Identifiers for databases, tables, columns, and indexes may be unquoted or quoted. An unquoted
identifier must follow these rules:

1. An identifier may contain all alphanumeric characters, the underline character (_), and
the dollar sign ($).

2. An identifier may begin with any of the legal characters, even a digit. However, it's best
to avoid identifiers that might be misinterpreted as constants.
For example, 1e3 might be taken as a number in scientific notation, whereas 0x1 might be interpreted as a hex constant, so neither is a good choice for an identifier.

3. An identifier cannot consist entirely of digits.



An alias identifier can include any character, but should be quoted if it's a reserved word (such as
SELECT or DESC), contains special characters, or consists entirely of digits. Aliases may be
quoted within single quotes ( '), double quotes, or back ticks.


Using Qualifiers for Table and Column Names


Column and table identifiers can be written in qualified form—that is, together with the identifier
of a higher-level element, with a period (.) separator.
A table name may be qualified with the name of the database to which it belongs. For example,
the Country table in the world database may be referred to as world.Country (note the .
separating the two identifiers in the name).

Well friends i this post i have discussed some topics that are very miniute but essential to be kept in mind during writting the code in mysql. In my next post i shall move into the concepts of mysql coding such as creating a database,table etc...

Mysql concepts made easy

MySQL INTRODUCTION
Mysql made easy to understand and with simple examples

What is Mysql????

MySQL, pronounced "my Ess Que El," is an open source, Enterprise-level, multi-threaded, relational database management system.

MySQL was developed by a consulting firm in Sweden called TcX. They were in need of a database system that was extremely fast and flexible. Unfortunately (or fortunately, depending on your point of view), they could not find anything on the market that could do what they wanted. So, they created MySql, which is loosely based on another database management system called MySql.

The product they created is fast, reliable, and extremely flexible. It is used in
many places throughout the world. Universities, Internet service providers and nonprofit organizations are the main users of MySQL, mainly because of its price (it is mostly free).

Lately, however, it has begun to permeate the business world as a reliable and fast database system. The reason for the growth of MySQL's popularity is the advent of the Open Source Movement in the computer industry.

The Open Source Movement, in case you haven't heard about it, is the result of several computer software vendors providing not only a product but the source code aswell.

Now lets us know what is Database????




A database is a series of structured files on a computer that are organized in a highly efficient manner.

These files can store tons of information that can be manipulated and called on when needed.

A database is organized in the following hierarchical manner, from the top down. You start with a database that contains a number of tables. Each table is made up of a series of columns. Data is stored in rows, and the place where each row intersects a column is known as a field.

let us know what are the Features of MySql:

Features of MySQL Relational database management system

• Multithreaded server
• Adheres to the standard set by the American national standards institute for sql
• Online help system
• Has Many APIs
• Portability
  

So guys i shall discuss about download and Working with mysql Basic commands in my next post.

PHP and MySQL Introduction

PHP and MySQL Introduction

This tutorial covers the basics of accessing a MySQL database from your PHP script. You will learn how to connect to a MySQL database, and how to execute a SQL statement.

MySQL is a popular relational database management system that is commonly used in PHP-based web applications. The free version of MySQL is called "MySQL Community Server" and can be downloaded here: MySQL Downloads. Installation instructions are available here: Installing MySQL Community Server. For the purposes of this tutorial, we will access a database named "helloworld". This database will have a table called Widgets with the following structure.
Column Name Data Type Modifiers
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT
name VARCHAR(32) NOT NULL
color VARCHAR(16) NOT NULL

The PHP language has built-in APIs for accessing MySQL servers. To access a database in MySQL from a PHP script, the first step is to connect to the MySQL server, using the mysql_connect() function. This function will return a connection identifier on success, and FALSE on failure. In the case of failure, the script can call mysql_error() to get an error message describing the failure.

// Replace these strings with the actual hostname:port,
// username, and password
$conn = mysql_connect("localhost:3306",
"username", "password");
if($conn === FALSE) {
die("Error connecting to database: "
. htmlspecialchars( mysql_error() ) );
}

The next step is to select which database the script will be accessing via the mysql_select_db() function. This function takes the name of the desired database, and returns TRUE on success and FALSE on failure.








// For this example we will connect to
// the 'helloworld' database
if(mysql_select_db('helloworld') === FALSE) {
die("Error selecting database: "
. htmlspecialchars( mysql_error() ) );
}

Now we are at the point where we can begin interacting with the database tables. The following code will select all records from our Widgets table, ordered by the name column. The mysql_query() function executes the SQL statement. For select statements, this function returns a result table on success, and FALSE on failure.

$statement = "select id, name, color from Widgets "
. "order by name";
$result = mysql_query($statement);
if($result === FALSE) {
die("Error executing statement: "
. htmlspecialchars( mysql_error() ) );
}

The following code will output an HTML table containing the contents of the Widgets table by iterating through the result table that was returned from mysql_query(). The mysql_fetch_assoc() function will be used to retrieve an associative array containing the next row of data from the result table. This function will return FALSE when all the rows have been retrieved.

// Iterate through the result table
echo('

Widgets

');
echo('');
echo('');
while($resultRow = mysql_fetch_assoc($result)) {

// The keys of the associative array
// are the column names
$id = $resultRow['id'];
$name = $resultRow['name'];
$color = $resultRow['color'];

echo('');

}
echo('

ID	Name	Color
' . htmlspecialchars($id) . '	' . htmlspecialchars($name) . '	' . htmlspecialchars($color) . '

');

We'll finish up this example by freeing the result table and closing the connection.

mysql_free_result($result);
mysql_close($conn);

This introduction should give you enough information to get started with using MySQL from PHP.

PHP Array Concepts with Easy Examples

PHP Array Concepts with Easy Examples

Here I have discussed about array concepts in php.

I hope people reading this blog must have known the basics of Php

just for reference i am giving small introduction of what PHP is??

PHP ==' Hypertext Preprocessor'.

Open Source, Serverside scripting Language.

Used to generate dynamic Web pages.

PHP scripts resides between reserved PHP tags.



This allows the programmer to embed PHP scripts within HTML pages.

Structurally similar to C/C++

Supports Procedural and OOP concept.




All PHP statements ends with a semicolon



PHP variables must begin with a "$" sign.

ARRAYS IN PHP

Now comin array concepts:

Definition 1:
An array is simply a collection of keys and their associated values. In PHP, the key can be either an integer or string, while the value can be any PHP data type.


In simple words array can be defined as follows.

Definition 2:
Arrays are special data types. Despite of other normal variables an array can store more than one value.

CREATE AN ARRAY:

In PHP, Arrays are created using the array() function:

$blankArray = array();
Here $blankArray is a variable in to which we are storing the array values.
array(//give no of array elements.)

Initializing Array:

An array can be initialized with a set of values by passing the values as parameters to the array() function: $anArray = array('A', 'B', 'C', 'D');

When initializing an array in the manner above, the values are automatically associated with integer keys in sequential order, starting at zero. The keys can be specified explicitly using the => operator:

$myArray = array('firstKey' => 'A'

	, 'secondKey' => 'B'

	, 19 => 'C'

	, 22 => 'D');



Retriving values from an array

Values can be retrieved from an array using the square bracket syntax below. If the key is a string, it is considered good practice to always use quotes.

echo $myArray['firstKey']; // A echo $myArray[19]; // C

Adding Values to an Array

Values can be added to an array using the syntax below:

$myArray['anotherKey'] = 'E';

If a key is not specified, the value will be associated with the largest previously assigned integer key plus one.

// If the largest integer key was 22, the key

// for the value below would be 23.

$myArray[] = 'F';

Removing Values from an Array

Use the unset() function to remove an item from an array.

unset($myArray[22]);  // Removes the item with key 22

Iterating Through an Array

The foreach operator can be used to iterate over the items in an array.

// Display the current contents of the array

foreach($myArray as $key => $value) {

echo $key . ' => ' . $value . '

';

}

Multi-Dimensional Arrays

In PHP, a multi-dimensional array can be created by making an array containing other arrays as values.

$multiDimensionalArray = array(

'A' => array(0 => 'red', 2 => 'blue', 3 => 'green'),

'B' => array(1 => 'orange', 2 => 'black'),

'C' => array(0 => 'white', 4 => 'purple', 8 => 'grey')

);



echo $multiDimensionalArray['A'][3]; // green

echo $multiDimensionalArray['C'][8]; // grey

here i have discussed few array concepts with their syntax.

I hope this post would give you the basic concepts of what an array is and how is it created and used.