[ciapug] MySQL conference in Omaha yesterday

[carl-olsen at mchsi.com]

Some of the things I learned yesterday:

1. Never use pconnect (the persistent connection function is going to be removed from PHP 6).

2. Always use prepared statements (requires mysqli).

3. Never use mysqli unless you are using prepared statements or other functions that do not exist in mysql (mysql runs faster).

4. Rarely use stored procedures because they are per thread (unlike other databases).  They are not compiled and stored like they are in other databases.  I think they are compiled at the start of a thread, but not permanently.  I'm not sure I caught all the details on this, but its possible that stored procedure functionality will be improved in future releases of MySQL.  Right now, their functionality is fairly crude.

5. Never use a function on a condition (filter) in a “WHERE” clause because indexing will be ignored.  The function has to run on every row, so it's just a sequential operation because of the function being used on the column.

6. Triggers are okay because they don’t run as often as stored procedures and have a lighter footprint.

7. EXPLAIN will show you how MySQL actually executes an SQL query (which is often different than the way it is written).

8. Two additional table types (in addition to InnoDB, MyISAM and others), are MEMORY and ARCHIVE.  I think the one that will likely get full text indexing as well as support for foreign keys was called "Falcon" or something like that (I never heard of it before).  MyISAM is going to run faster than InnoDB in most cases because it is not as complicated as InnoDB.

9. To get a query to show vertically from the command line instead of in a table use “/G” switch at the end of the query. 
 
--
Carl Olsen, MCSE
Web Developer
Marketing and Communications
Drake University
2507 University Avenue
115 Old Main
Des Moines, Iowa 50311-4505
Phone: 515-271-2986
Fax: 515-271-3798
Carl.Olsen at drake.edu
www.drake.edu