Archive for the ‘Database’ Category


InnoDB stores data in a file(s) called a tablespace. In newer versions of MySQL, you can store the data and indexes in separate files. MyISAM stores the data and indexes in two files (.MYD, .MYI). InnoDB is extremely critical about its tablespace and log files (They normally should be backed up together). You can’t just backup your data by copying files like you would with MyISAM. In some situations you can only restore a table to the server from which you backed it up!

InnoDB are built on clustered indexes and uses MVCC to achieve high concurrency. This provides very fast primary key lookups. MyISAM doesn’t support transactions, FK contraints, or row-level locks. MyISAM uses shared and exclusive locks on the entire table. However, concurrent reads & inserts for new rows are allowed.

InnoDB is crash safe (Assuming your flushes are truly durable on disk and not on some volatile cache). MyISAM is no where close to being crash safe. If you care about your data, use InnoDB. It might be OK to use MyISAM for read-only workloads.

MYISAM

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB
6. InnoDB has better crash recovery.

Advertisements

Before starting adding numerous indexes in the tables, try to analyze the performance of the whole database, the whole set of slow queries, examine again the choices of the datatypes, the engine you use and the configuration settings.

In a very large DB, very small details in indexing and querying make the difference between smooth sailing and catastrophe.

Although it is useful from a conceptual standpoint to think about which fields should be indexed during creation, it is simple to add indices to pre-existing tables as well. You can add one like this:

CREATE INDEX index_name ON table_name(column_name);

Another way of accomplishing the same thing is this:

ALTER TABLE table_name ADD INDEX ( column_name );

Now, we can run a simple query on a large dataset:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;


Let’s take an example table called “sample” with only two rows – “number” and “employee”. If you run a simple SQL query such as:

SELECT * FROM sample WHERE number = 4;
MySQL will check all records and will return only the one that has its number value set to 4.

But if you have several thousand entries for example, this will be a slow query. In this case we have a unique field – “number”. Therefore, we can create an index for it. Indexing will create an internal register that is saved in by the MySQL service. It can be done with the following query:

ALTER TABLE sample ADD INDEX (number);

Once this index is set, next time you want to get the information for employee number 4, the service will go directly to it using the index and will return the information much faster.
This is just a very basic example. For bigger databases, the difference in the loading time can be significant. Indexing your database can drastically decrease the loading time of your web applications.

There is another query that you can use to increase the loading speed of your database:

OPTIMIZE TABLE sample;

You can also check our tutorial below which will help you to Repair/Optimize your database using phpMyAdmin:

How can you see all indexes defined for a table?

Indexes are defined for the table by:

SHOW INDEX FROM ;