The Spanner - PHP Mysql tips. Monday, 23 July 2007 Continuing from my earlier post on PHP performance, I thought I’d share a few Mysql tips that I’ve learnt over the years.
Hope it helps someone and please leave a comment with your own tips or provide any corrections to the ones mentioned. Word searching SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword') (Fastest) SELECT * FROM table WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE) (Fast) SELECT * FROM table WHERE RLIKE '(^| +)Keyword($| +)' SELECT * FROM table WHERE RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])' (Slow) Contains searching SELECT * FROM table WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE) SELECT * FROM table WHERE field LIKE 'Keyword%' SELECT * FROM table WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE) SELECT * FROM table WHERE field LIKE '%Keyword%' Recordsets SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE Condition LIMIT 0, 10 SELECT FOUND_ROWS() (Very slow) Joins Returns all products with a matching supplier.
SQL TOOLBOX: 20+ Tools and Lessons About SQL. SQL is all around us, but not everyone really knows its inner workings.
We've gathered 20+ tools and lessons to help you learn all about it. 25 Commandments for MySQL Development - A list of 25 things all MySQL designers should keep in mind as they work. Comparison of different SQL implementations - A simple page comparing all the different ways you can use the SQL language. Instant SQL Formatter - Open several different forms of databases and output them in to a language of your choice including SQL. Introduction to SQL - An introductory lesson to SQL, covering pretty much all the basics. Learning SQL Using phpMyAdmin - A tutorial for learning the popular phpMyAdmin interface for MySQL database management. MySQL Basics - A basic tutorial for those just starting out with learning MySQL. MySQL Cheat Sheet - A quick reference for some of the most common commands in MySQL. Oracle SQL Developer - A free graphical tool for developing an SQL database. I/O Reader - 15 Cool Things About PHP That Most People Overlook. Note: I have followed this post up with 12 Things You Should Dislike About PHP.
I saw this article linked to from Digg and was disappointed by the list. Here's what I think are some of the more interesting features of PHP. 1. Reflection API PHP5's Reflection API can introspect PHP functions and classes and even allow them to be rebuilt (see this demo)! Class TestCallFunc { public function __construct($a, $b) { echo $a .' '. Unfortunately the above example hardly does the Reflection API justice. 2. PHP has an unusual statement called declare. 3. list(), extract(), and compact() list() is a function that PHPers learn early and forget about almost as quickly. list() allows for easy unpacking of numeric arrays, eg: array('a', 'b', 'c', ...). $a = 'a'; $b = 'b'; list($a, $b) = array($b, $a); echo $a; // output: b echo $b; // output: a List also has a practical application for any function that returns an array. Unpacking associative arrays is also easily accomplished with extract(). 5. Straw Dogs Code Blog » 20 Indispensible MySQL Resources.
Science.gov : USA.gov for Science - Government Science Portal.