Best compression

[cwarn23]

Hi and I am looking for a compression mechonism that can compress text (just letters and numbers) into binary or all weird symbols. Does anybody know of any good php/mysql codes as I can't get any to work.

[cwarn23]

I have tried running the following mysql queries through the phpmyadmin command console (mysql_query() code) but I seem to be unable to retrieve the value. Here is my code.

INSERT INTO `table` SET `column`=COMPRESS("abcd")
SELECT * FROM `table` WHERE UNCOMPRESS(`column`)="abcd"

Also tried

INSERT INTO `table` SET `column`=COMPRESS("abcd")
SELECT * FROM `table` WHERE `column`=COMPRESS("abcd")

Please advice on how I should use the select query.

[cwarn23]

I think I may have found something with the bzip2/bz2 library because the gz library didn't compress very well. I will try storing the bzcompress() result in a binary column. Well let you know how my result goes.

[digital-ether]

I have tried running the following mysql queries through the phpmyadmin command console (mysql_query() code) but I seem to be unable to retrieve the value. Here is my code.

INSERT INTO `table` SET `column`=COMPRESS("abcd")
SELECT * FROM `table` WHERE UNCOMPRESS(`column`)="abcd"

Also tried

INSERT INTO `table` SET `column`=COMPRESS("abcd")
SELECT * FROM `table` WHERE `column`=COMPRESS("abcd")

Please advice on how I should use the select query.

I tried this and it worked:

SELECT * FROM `table` WHERE `compressed` = compress('test')

The only thing I can assume is the column containing the compressed data has to be a blob or varbinary type.

[cwarn23]

I just tried varbinary like before and again varbinary didn't work however I tried blob which I have never heard of before and it worked! Also the bz library works but won't decompress. So using the method you have described how can I decompress the result?

[digital-ether]

I just tried varbinary like before and again varbinary didn't work however I tried blob which I have never heard of before and it worked! Also the bz library works but won't decompress. So using the method you have described how can I decompress the result?

Which bz library? Do you mean zlib? Is this in mysql or PHP?

You can just decompress the result in the SQL query:

SELECT uncompress(test) as test FROM `table` WHERE `compressed` = compress('test')

This should give you the column test in decompressed form.

Note:

I wouldn't use:

SELECT * FROM `table` WHERE UNCOMPRESS(`column`)="abcd"

The reason is that you'll run uncompress() on each row in the database table.
You will also be unable to use any index made on that column.

If you use:

`column`= COMPRESS("abcd")

Then you only have to compress the input "abcd" once. You will also be able to use indexes made on the `column` column since you're comparing directly.

[cwarn23]

SELECT uncompress(test) as test FROM `table` WHERE `compressed` = compress('test')

Wow, if that works that will be brillient. I will try it out.

And the confusion you may have had before about the bz library also known as bzip2 or bz2 depend on what version (I found out the hard way) that was another option I was exploring untill I found out it couldn't compress crc32 hashes. So as I said, if that above code works, that will be the solution to all my endless reverse hashing storage problems.

[digital-ether]

Some good info here: http://www.mysqlperformanceblog.com/2008/01/11/mysql-blob-compression-performance-benefits/

[cwarn23]

It works!
Even though it requires twice as much space on the mysql cache it works perfectly. Thanks for that very usefull code that I could not find anywhere else.

[digital-ether]

It works!
Even though it requires twice as much space on the mysql cache it works perfectly. Thanks for that very usefull code that I could not find anywhere else.

I wouldn't worry about the cache. But if you want, you can uncompress the result on the PHP side.

Assuming you're using the default compression, zlib, then you need to cut off the first 4 bytes before you uncompress the data.

The first 4 bytes is reserved for the length of the uncompressed string.

see: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_compress

Here if a full example using PDO:

<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=test;host=localhost';
$user = 'root';
$password = '';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT binary(compressed) AS compressed FROM `compressed` WHERE compress(:test) = compressed ';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':test' => 'test'));
$row = $sth->fetch();

// compressed result, with first 4 bytes being the length
$result = $row['compressed'];

// the length of uncompressed string
$len = substr($result, 0, 4);
// the compressed string
$compressed = substr($result, 4, strlen($result)-4);

// uncompress the string
echo gzuncompress($compressed, intval($len)); // test

?>

Edit:

Here is the structure of the table I used in the example:

+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| value      | text | NO   |     | NULL    |       |
| compressed | blob | NO   |     | NULL    |       |
+------------+------+------+-----+---------+-------+

And the dump:

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `compressed`
--

CREATE TABLE IF NOT EXISTS `compressed` (
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  `compressed` blob NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `compressed`
--

INSERT INTO `compressed` (`value`, `compressed`) VALUES
('test', 0x04000000789c2b492d2e0100045d01c1);