When we deal with large amounts of data in MySQL, operations such as SELECT statements can start to take a long time if we have too many rows to search. Consider if we had a massive list of phone numbers and names on a sheet of paper - millions, billions even. Think about how long it would take to look through all those numbers for a specific name, especially if the list is completely disorganised, in no specific order.
Now, think about how much easier it becomes if that list is given to you in the form of a phone book, with all the names listed alphabetically, with handy page labels telling you which letter of the alphabet you’re on. You can quickly jump to the section you’re looking for and, with a small amount of extra searching, come across the value you’re interested in.
This is similar in some respects to the way MySQL uses indexes. If you have a million names in a database table, all with relevant records, and wanted to find a record with a specific name, then by defining an index on that particular column (the column “name” for example) then you can speed up that search enormously.
Indexes aren’t just limited to alphabetical searches either. You could define an index on a table column containing dates, so you can locate entries on a specific day, for example, or an index on a table column containing salary values to speed up the process of finding people with a salary greater than a given value.
MySQL allows you to create four distinct types of index:
- Primary Index - Only one primary index can be defined per table, and this is usually reserved for the autoincremental ID for each row. I’ll mention this again in a minute, to explain how useful it can be. Primary indices must be unique, which explains why we cannot repeat names in our employee table
- Unique Index - Like the primary key, this index is unique, and you cannot repeat values, but you can apply a unique key to as many columns in a table as you like
- Index - A standard index. Values within a column that acts as a standard index can be repeated, so this is generally the most useful kind of index to use. Unique indices are handy if you wish to specify that columns must contain non-duplicated values though
- Fulltext index - A useful index which can be used on columns containing entries that have lots of text in them. This index creates an efficient way to search through vast amounts of textual information
Let’s go back to our original table definition, containing our list of employees, and make a little adjustment
| ID | Name | Salary | Department | Job Title |
| 1 | Jim | 32,500 | Sales | Manager |
| 2 | Bob | 36,000 | Sales | Supervisor |
| 3 | Jeff | 35,000 | Accounting | Manager |
| 4 | Alan | 27,000 | Accounting | Staff |
| 5 | James | 27,500 | Accounting | Staff |
We’ve not made much of a change here, except to add a new column at the start - “ID”. This column simply contains a number, greater than 1, and we can give this column a special property, called AUTOINCREMENT - This can be set when creating the table within PHPMyAdmin or equivalent
This new table can be created in MySQL by executing the following MySQL query
DROP TABLE IF EXISTS `employees`; CREATE TABLE IF NOT EXISTS `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `salary` int(11) NOT NULL, `department` varchar(32) NOT NULL, `position` varchar(32) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; INSERT INTO `employees` (`id`, `name`, `salary`, `department`, `position`) VALUES (1, 'Alan', 27000, 'Accounting', 'Staff'), (2, 'Bob', 36000, 'Sales', 'Supervisor'), (3, 'James', 27500, 'Accounting', 'Staff'), (4, 'Jeff', 35000, 'Accounting', 'Manager'), (5, 'Jim', 32500, 'Sales', 'Manager');
(Notice here how we’re defining the column “id” to be a PRIMARY KEY, and the column “name” to be a KEY. This means we still have an index on the column “name” which allows us to search quickly for a specific name, but still have duplicate values)
What autoincrement does is to specify that whenever a new entry is placed into the table, the value stored in the AUTOINCREMENT column increases by one. This ensures that the column never contains a duplicate value, while also allowing all the other columns to contain whatever values you wish. You can then assign the ID column to be a primary key
Now, if we run the following two queries
mysql_query("INSERT INTO employees (name, salary, department, position) VALUES ('Keith', 28500, 'Sales', 'Staff')"); mysql_query("INSERT INTO employees (name, salary, department, position) VALUES ('Keith', 29750, 'Accounting', 'Staff')");
We will now have two employees named Keith - one in Accounting being paid more than the “Keith” in Sales. Our employee table should now look like
| ID | Name | Salary | Department | Job Title |
| 1 | Jim | 32,500 | Sales | Manager |
| 2 | Bob | 36,000 | Sales | Supervisor |
| 3 | Jeff | 35,000 | Accounting | Manager |
| 4 | Alan | 27,000 | Accounting | Staff |
| 5 | James | 27,500 | Accounting | Staff |
| 6 | Keith | 28,500 | Sales | Staff |
| 7 | Keith | 29,750 | Accounting | Staff |
See how the two new rows we’ve inserted have had new ID values automatically created for them? (rows 6 and 7) The main reason behind having a unique ID for each entry in the table will become more apparent in our next section about Normalization (or, Using Multiple Tables to Store Data), where we will see how we can use multiple tables to make our database more organized, and make it easier for us to keep track of records and change existing ones by using these IDs to link tables together in a meaningful way, and create relationships between the data.
September 28th, 2008 at 8:34 am
excellent !!
thanx for ur help. i got the exact answer to my query.
October 8th, 2008 at 10:17 pm
Just seen small problem with the example.
$result = mysql_query(”INSERT INTO employees (name, salary, department, position)
VALUES (’Keith’, 28500, ‘Sales’, ‘Staff’)”);
return error #1064 when used directly in phpmyadmin;
INSERT INTO employees (name, salary, department, position)
VALUES (’Keith’, 28500, ‘Sales’, ‘Staff’)
Gives the expected response.
Still a great tuto tho.
Colin
Col
December 2nd, 2008 at 11:34 am
hi….
i m having a problem of inserting distinct records into database.
my question is how to insert distinct records into db.
Help!!!!