Archive: mysql

Just came across this forum post of MySQL that looks to be a gold mine of information related to performance tuning MySQL. For reference I am copying and pasting the full thing here though all credit goes to the above. Book: High Performance MySQL (2nd Edition) http://www.highperfmysql.com/ MySQL Performance Tuning – Best Practices: http://jpipes.com/presentations/perf_tuning_best_practices.pdf MySQL [...]

Many database queries rely on seeing if two columns match / differ, however sometimes you may want to check if either column contains a sub-string of the other. MySql allows you to do this, but it isn’t that well documented, so hopefully this will help someone looking for this in the future SELECT * FROM [...]

When migrating one server to another you often hit bumps in the road. Todays was transferring a database from one server to another. During this standard procedure I found that the restored database was missing a few tables. Irritating as Magento doesn’t like missing tables. Digging down into the backup and extracting the first missing [...]

If you need to update a large number of rows on a single table then your first reaction may be to write a loop that updates one row at a time. Of course if the table is large then this can result in a very large number of SQL queries. Taking a bit of inspiration [...]

Today I was working on an oscommerce site and took a backup of their database, created the usual details from their config file on my local mysql and the above error came up (with admin@localhost for what it's worth). Upon investigation, a trigger had been created on the database whilst logged in via a different [...]

MySQL has a nice feature that you may well not have used called Triggers. These are little SQL statements that are tied to events on table and then perform some extra work. The idea being that you will check the values for a row and if they meet some conditions you will take some further [...]

If you are struggling to get some foreign keys set up on your MySQL InnoDB database then perhaps this is your problem and a pretty simple solution. If you are getting errno 150 then take a look at the data types for the two fields that you are trying to relate to each other. I [...]

One of the advantages of using InnoDB as your storage engine in MySQL is that you can create a database structure that will automatically keep itself clean and tidy, by having deletes cascade through your tables as soon as you delete the main entity. However, if you need to empty a large table, you will [...]

If you need to change the character set and collation for all columns and tables on an entire database then check out this little PHP script. It's currently Zend Framework based, though only for the database adapter - you could easily modify this to suit your own database connection. PLAIN TEXT PHP: <?php /* collationChange [...]

Check out this little query for checking for obviously invalid EANs. Note this is not checking the check digit for validity, it is purely looking for data that is in no way possibly an EAN number. PLAIN TEXT SQL: UPDATE products SET ean = '' WHERE ean != '' AND ean NOT REGEXP '^[0-9]{13}$' assuming [...]