Databases

1. You can type commands into a character-based user-interface program, and see a response. e.g. you'd type a 'create table...' command to create a table, and then see a '...table created ok!' response.
   
   
2. You access the database from a program you've written. The language you use has to have special functions to:
   • Connect to the database
   • Submit a query
   • Get the results back, line by line.
   • Disconnect from the database

• Report Generators and 'Data Mining' software.
• Programs to load / save entire databases.
• Programs that automatically synchronize two or more databases.

All Relational Databases use indexes. Similar to the index in a book, indexes provide a quick way to find the exact data item you want.

Imagine you have a database of 100,000 customers, and you want to find just one. If you just read the 'customers' table from start to finish until you find the one your searching for, you could end up having to read all 100,000 records. This would be very slow. Most relational databases use a b-tree index structure. This is a clever algorithm that guarantees that you can find a data item by reading at most 3 rows from the index. Databases commonly have millions of rows - so you can see the necessity for indexes!

Indexes are a large part of databases and their design. Defining a column as the primary key implicitly creates an index. If you have a primary key on a table - it has an index.

You can add a number of indexes (up to 32 in MySQL) to each table you have. You'd use the create index command - more later...

Indexes are used automatically by the database itself when you issue a query (ask for data). It uses the index to find the data in the table . For example, we want to get a customer's details from the example 'customers' table above...

If we submit the following query, the database will use the index it created for our primary key column 'customer_id', and get everything for customer 1:

 select * from customers where customer_id = 1;

The database uses the index because it can use it. The query contains the 'customer_id' so it can look in the index and find the location of customer '1'.

However, if you only have a street address for the customer, you have to write the following query to get the same data, and the database can't use the primary key index because it doesn't have a customer_id:

 select * from customers where addr1 = "3 The Cottages";

If we do this query often, we can add an index on the 'addr1' column to the 'customers' table. Then the database will be able to use that index.

If there's no index on the column in the query, the database will have to go through the whole table! This is called a full table scan

MySQL is blindingly fast and will do a full table scan, even on large tables, in a very short time. It's barely worth indexing tables of one hundred rows or less. However, when you've got more than a few hundred rows in a table, you should definitely start to think about putting indexes on your most queried columns.

INFO: When you issue a query to the database, the 'Query Optimiser' first decides how it will execute the query - which order to join the tables, which indexes to use etc. You can provide hints to some databases to tell them which indexes to use. Very small tables don't benefit from being indexed. If you had a 'customers' table with just a few customers in it, then a full table scan would be quicker than going via the index. As a general rule you should provide a primary key for all tables, and only provide additional indexes when you have hundreds of rows, and you know that you're going to be running queries that select data using a column other than the primary key.