www.zeger.nl

When building a serious website you should always use a database for the session handling; this has several advantages of which session sharing between web servers [load balancing] is a key feature. Usually MySQL is the database of choice for PHP programmers, when using MySQL we can choose between two table types: InnoDB or MyISAM. Which type to choose for database session handling?

Firstly a small overview of key featurs of both InnoDB and MyISAM

MyISAM

Fast select queries, each INSERT or UPDATE statement locks the table.

InnoDB

Select queries are slow compared to MyISAM, each INSERT or UPDATE statement locks the row of the corresponding session.

Secondly it is clear that for session handling the number of SELECT queries is equal to the sum of the UPDATE (current session is being updated) and INSERT queries (new session has been created).

So if we assume you run a small website where rarely ever more than one person is on your site the choice of database type is irrelevant. In fact if you can stop reading now and just use the MyISAM type since the SELECT statements outperform InnoDB.

If you run a website having a lot of concurrent users, especially AJAX driven sites where the session is being used on the server side, you could run into trouble when using MyISAM. After closing the session the data is committed to the database using an UPDATE/INSERT statement thus locking the entire table, which in turn is putting all SELECT queries on hold until the UPDATE/INSERT statement has been finished. All pageviews and session based AJAX calls have to wait for a session of a single user to be closed: for high-traffic ajax driven websites this results in an increased page loading time. For each pageview the site is put "on hold" for a short amount of time.

If we use InnoDB as the session table type an UPDATE/INSERT statement only locks the current row of the session table. Multiple sessions can be closed at the same time thus the page loading time wil not increase due to locking effects of the database.

Conclusion
For small websites (99% of all sites) it really does not matter which table type you use, in fact you should use MyISAM for maximum perfomance. For (ajax-driven) websites having concurrent users you should use the InnoDB table type SELECT statements will be slower but overall performance will greatly improve.

Development
We could use the MEMORY (used to be HEAP) table type to store the current sessions and use the garbage collect function of the session handler to move sessions between the regular table and the MEMORY table.

Also check the MySQL PDF for a very MySQL minded approach to database session handling.