Have you tried reversing the order of your tests, to see if there is some influence from caching?
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -----Original Message----- > From: John Kraal [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 23, 2007 8:51 AM > To: mysql@lists.mysql.com > Subject: Query performance plain/text versus AES_DECRYPT(): LIKE %..% > > 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
