W dream

Posted on Sunday, 3rd May 2009 by Balazs

To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique:

CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So [...]

Tags: DB2, IBM, MySQL, Oracle, PostgreSQL
Posted in DB2, Technology | Comments (Comments)

Posted on Sunday, 3rd May 2009 by Balazs

To delete dupplicate records in SQL, the following sequence of commands will do the trick:

CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary [...]

Tags: DB2, IBM, MySQL, Oracle, PostgreSQL
Posted in DB2, Technology | Comments (Comments)

Posted on Friday, 1st May 2009 by Balazs

MySQL allows to select a different king of engine on a per table basis at creation time of each table. Each engine has its advantages and caveats. Here is a brief summary:

MyISAM: fastest disk based, least space requirement, non-transactional, slow crash recovery
InnoDB: slowest engine, transactional, fastest crash recovery
HEAP:fastest overall engine, limited by [...]

Tags: MySQL
Posted in Technology | Comments (Comments)

Posted on Friday, 1st May 2009 by Balazs

When inserting into regular database systems, like Oracle, DB2, and even PostgreSQL, the omitted attributes from an INSERT are automatically set to default. Not so with MyISAM tables on MySQL. The syntax required by MySQL for an implicit default when multiple attributes are inserted is DEFAULT. For example:
INSERT INTO my_table VALUES (?,?,DEFAULT,?,DEFAULT)
will [...]

Tags: MySQL
Posted in Linux | Comments (Comments)

Posted on Saturday, 3rd January 2009 by Balazs

For those of us that are still forced to used 32bit MySQL, there is a table size limit of 4GB by default (even though the file size limit on those systems is 4TB on ext3 – 2TB on NTFS).  Here is what to do to lift that limit:
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
You have to specify [...]

Tags: MySQL
Posted in Technology | Comments (Comments)

Posted on Saturday, 3rd January 2009 by Balazs

Exporting from an RDBMS always depends on the syntax of the specific RDBMS in question.  For MySQL, here is how to transform a SELECT statement into a CSV export:
SELECT a,b INTO OUTFILE ‘/tmp/result.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’ FROM test_table;
Reference: MySQL 5.0 Reference Manual

Share and [...]

Tags: csv, MySQL
Posted in Technology | Comments (Comments)

Posted on Saturday, 3rd January 2009 by Balazs

Even though for our core systems we have migrated most databases to DB2, we still have to deal with MySQL for some side systems, such as CRM, Blog, etc.   One of the MySQL features that I recently noticed is that dropping indexes on sizable tables takes forever.  That is apparently due to a convoluted [...]

Tags: drop index, myisamchk, MySQL, SQL
Posted in Technology | Comments (Comments)