[ciapug] MySQL conference in Omaha yesterday

[carl-olsen at mchsi.com]

Another interesting thing was query caching.  If you use the MySQL function for calculating a date inside a query, it will not cache the query - but always executes the query.  Using a PHP function to calculate the date creates a static query string and MySQL will cache the results and return the result from cache instead of re-running the query every time it is called.

 -------------- Original message ----------------------
From: 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
> 
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug