Dear you,
I've been working on encrypting some data for a customer. They want
their personal/sensitive information encrypted in the database, but they
want to be able to search it too, through the application. So we've been
thinking a bit, and just started trying and benchmarking some solutions
we thought up.
The next one really got my attention, I created a table with 4 fields:
1. id (primary/auto_increment, not really interesting)
2. "field", with encrypted data
3. md5sum (it has no special use, we benched it though.)
4. "line", always containing three words (the same three as encrypted)
When we started querying the table for random words (from lipsum.com),
it seems that searching in the encrypted fields was _lots_ faster.
Results below:
1.000 queries per field:
~$ php -q searchtest.php
Control test (plain/text LIKE %..%): 1.409699s
Decrypt test (AES_DECRYPT() LIKE %..%): 1.226069s
done
1.000.000 queries per field:
~$ php -q searchtest.php
Control test (plain/text LIKE %..%): 155.059671s
Decrypt test (AES_DECRYPT() LIKE %..%): 137.003216s
done
Actually, the only thing I could think of to say was: "Well, at least
it's consistent".
I've attached all the files I used for this test. Edit db.inc.php (add
some more lipsum if you want), execute fill.php, and then have fun with
bench.php.
Does any of you know why this is, how come, etc? I'm just very curious.
Regards,
John Kraal
--
/ Humanique
/ Webstrategie en ontwikkeling
/ http://www.humanique.com/
-
Humanique zoekt een ervaren Web-ontwikkelaar (PHP).
Bekijk de vacature op http://www.humanique.com/
-
-- MySQL dump 10.10
--
-- Host: localhost Database: hmnq_test
-- ------------------------------------------------------
-- Server version 5.0.30-Debian_3-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `encryptie`
--
DROP TABLE IF EXISTS `encryptie`;
CREATE TABLE `encryptie` (
`id` int(11) NOT NULL auto_increment,
`field` blob NOT NULL,
`md5sum` varchar(32) NOT NULL,
`line` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `md5sum` (`md5sum`),
KEY `line` (`line`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `encryptie`
--
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2007-10-23 12:17:47
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
