Hi Reza, I agree with your query optimization suggestion. In my case I did need to query by UA, so it made sense to me. Nonetheless you want to be able to control that you don't have UA duplicates. This is, of course, if you assume that two different devices cannot have the same UA.
In my case I was looking up by UA strings, which I consider unique, and indexing that field in particular created a larger clustered index (I use InnoDB). So by hashing the UA string I, more often than not, achieved a shorter identifier and therefore a more economical clustered index. However when you take into account UA strings such as these ones: NokiaN70-1/5.0638.3.0.1/SN352913029533XXX Series60/2.8 Profile/MIDP-2.0 Configuration/CLDC-1.1 NokiaN70-1/5.0737.3.0.1/SN358080019686XXX Series60/2.8 Profile/MIDP-2.0 Configuration/CLDC-1.1 Then it's obvious that UA Strings are not the best way to identify a single device. As it has been mentioned in the past, there's a pattern, so probably the most accurate way of uniquely identifying a device is the UA String pattern rather than the UA String itself which, for my sins, have not been able to get working on :) Regards, On Wed, Mar 27, 2013 at 3:38 PM, Reza <[email protected]> wrote: > So I kind of consider myself knowledgeable in the DB arena... What is the > query use case where we are doing a lookup on the hash of a user agent? > > So I generally optimize my data around the queries. In this case, I only > see us querying individual rows by their unique id (auto generated integer) > and maybe by its state and who its assigned to. The actual user agent > string is just metadata... no? > > > ________________________________ > From: eberhard speer jr. <[email protected]> > To: [email protected] > Sent: Wednesday, March 27, 2013 10:02 AM > Subject: User Agent table > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > With you regard to the User Agent table : > > if your database supports GUIDs you can use that to implement Carlos' > idea : MD5 is 16 byte (128 bit) so you can easily convert it to a GUID. > > Regards, > > esjr > > > Hi, > > > > If I could suggest a small change in order to make a better index > > on that table. I made a similar table at one point, but for faster > > lookups I… (I know I'm about to be butchered)… I hashed the UA > > string…. using md5 no less, but I must say that +500K devices later > > I haven't had a single problem. Also, it would be a good idea to > > add a column that points to the device's information source (in my > > case it was WURFL/UAProf). > > > > Good stuff on this thread. > > > > Regards, > > > > -- Carlos D'Agostino > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.8 (MingW32) > Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ > > iQEcBAEBAgAGBQJRUvwMAAoJEOxywXcFLKYcK8YIAKNzPONJql0OVF/WIRq5MJuc > iXNo09CDv3tVpVEclnh6zobtwoFfXwiuoNsMl2+uVgAkxVeoZKQJjzmNrMfW4hDx > SbG33s/mTocSa6GrLtmZey9C1ivfS7cB5UQanSWCWLZ/gUqgmgUwIfdT+UDMAkU7 > FM43P3zBdIKQfwK8P7eSwarKoINrn2irUuMU2NawHv8H1ZepiHydWWkHsmKQDz6Q > QVoKkzsRqmys3GeZzc8BAgIHBA73m35BjY5Rt1Q3jm6D/kuXB1+2AVr/nftNL149 > 2fhizeDfBBblOXWboCnSYCAXYiiUK/iOfOZmGENb2b5ahAszqqAqYQmEkCTiPoU= > =6oFf > -----END PGP SIGNATURE----- > -- Carlos D'Agostino.
