April 20

MyISAM or InnoDB MySQL engine?

If you’re curious about the difference of MySQL engines, here they are.

Comparing and contrasting what MyISAM is and InnoDB. First ISAM is the acronym for Indexed Sequential Access Method, generally it is fast, compressable, and allows full text searching.  Second InnoDB is transaction safe and includes automatic foreign key checking so it’s a bit slower.

The important part is that don’t spend too much time deciding which one to choose, because you can change table type later with the ALTER TABLE statement.  Instead you should focus on optimizing tables for performance such as using fixed with fields versus variable width (varchar).  Now for the technical details.

MyISAM

MyISAM tables are optimized for flexibility and compressionThey can hold upto 256TB of data but are not transactional-safe, meaning if the power goes out in a middle of a write, the data is gone.  This is especially important for financial data.  The schema is flexible so it is good for beginners learning about databases.  Foreign key relationships do not have to be setup for the database to function, however this can cause poor performance and unreliable data.  The simplicity allows lower use of hardware resources and another benefit is the table search ability for full-text.
Continue reading

Category: Code | LEAVE A COMMENT
March 7

How to update a MySQL field in a table matrix

matrix table image

This is a tutorial on updating a unqiue mysql field in a matrix grid of elements by using PHP and jQuery.

In this tutorial, we are presuming you are familiar with PHP and MySQL.
This is the example we will use. The scenario is giving users access to special features on movies created by producers. This will be useful in the near future when producers have control of film distribution.

We start with the database design creating four tables.

  1. producers
  2. users
  3. Features
  4. feature_access

Step 1. Import schema

Copy paste the code or import the TEST.SQL file into phpMyAdmin (download file link at the bottom of tutorial).

The USERS and PRODUCERS tables are simplified to id and names. The FEATURES table has a foreign key to link the producer who made the feature. The FEATURE_ACCESS table contains 3 foreign keys, linking the 3 other tables. The additional field we are calling “access” is a BOOLEAN allowing NULL.
If you are wondering why were are allowing NULL values besides 0 and 1, it’s because we want the FEATURE_ACCESS records to exist regardless if they are active or not. More on this later.

Continue reading

Category: Code | LEAVE A COMMENT