RSS Feed
TwitterMydump and gzip of database dbuser:pass@localhost/dbname into remote machine “destination.com”, file ~/dbname.sql.gz mysqldump -h localhost -u root -ppass dbname | gzip \ | ssh user@destination.com “cat > ~/dbname.sql.gz”
Archive for the ‘DB and MySql’ Category
Posted in 
MySQL REPLACE statement – When to use it
Posted by admin on June 19th, 2011
MySQL implements an useful function -Â REPLACE INTO -Â Â that developers should use to avoid coding logic already implemented at the DB level. That function works exactly as an INSERT (same syntax), but it also performs a DELETE on the existing record when a duplicate-key occurs (same PRIMARY KEY or UNIQUE KEY on another column) in the [...]
Optimising Zend Framework applications (1) – cache db objects, PHP code profiling and optimisation
Posted by admin on February 27th, 2011
I’m optimizing some zend framework applications these days and I’ve been reading, researching, optimising and measuring results. Some links to read before: Optimising a Zend Framework application – by Rob Allen – PHPUK February 2011 Profile your PHP application and make it fly – by Lorenzo Alberton – PHPNW 9 Oct 2010 The optimisation process [...]
Implementing page counter (MySQL memory temporary table + MySQL procedure)
Posted by admin on January 29th, 2011
Although there is google analytics, web applications often have to save statistics of visits (IP, page visited, timestamp) in order to make some business logic. Example: websites with articles (blog or newspaper) with features like “most read articles”, “articles most read from the visitors of the current article” etc… Save visits into a MySQL memory [...]
Automatic caching of Zend_Db (or any object) calls
Posted by admin on January 24th, 2011
I’m working on a personal Zend framework application that uses Zend_Db_Table_Abstract classes to get data from the database. Each class has its own methods (getall, getXX, getYYY) etc… My need was having all those method calls (around the code) automatically cached. The first solution in my mind was a general wrapper (container) class that uses [...]
Profiling MySQL
Posted by admin on November 19th, 2009
To analyze the db server usage in a complex PHP application, the first step is to profile the db server.There are lots of tools to profile, but I think it’s very easy to make a customized code to save the data really needed.The idea is save information about some queries in the production environment (about [...]
MySQL dump importing
Posted by admin on November 19th, 2009
Today I realized that “mysqlimport” is not working as expected on Wamp environment.A working way to import a sql/dump file is to use the “mysql” executable #localhostmysql –u root -p –user=root –force [DBNAME] < [FILE.SQL]
Mass rows copying (duplicating) with filed customization – MySQL
Posted by admin on September 28th, 2009
Let’s suppose we have a table with the following structure and data: # `table`id | a | b | c |—————————1 | “aaa” | “xxx” | “ccc”2 | “aab” | “yyy” | “ccc”3 | “aba” | “yyy” | “ccc”4 | “abc” | “xxx” | “ccc”5 | “dcz” | “xxx” | “eee” Now, we want to [...]
MySQL : version differences notes (draft)
Posted by admin on June 10th, 2009
Relevant features added in Mysql 4.0 FULLTEXT search INNODB tables (and on [delete|update features] UNION statement TRUNCATE TABLE statement multiple table delete and update Relevant features added in Mysql 4.1 Subquery (nested query) supported “CREATE table LIKE table 2″ syntax supported added storages:- EXAMPLE (for developers)- NBCLUSTER (table partitioned over many computers)- CSV (comma separated [...]
INNODB storage engine: notes
Posted by admin on June 8th, 2009
Advantages transactions row-level locking foreign key constraints ! and behaviourON [DELETE|UPDATE] [RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT] Disadvantages INNODB does NOT support FULLTEXT index (MyISAM does) not initial value for auto_increment keys
