sql - Store hashes of User-Agent strings in a MySQL table: insert if not exists, return id -

Inspired by the following two answers on stack overflow, I implemented a table with the goal of storing user-agent strings in it;

Here's my table structure:

  Make the Make Table, otherwise the UIString (Ua_id Primary Key AUTO_INCREMENT , Ua_hash BINARY (16), u text, unique key ua_hash (ua_hash));  

I would like to receive the following:

• Input: User-agent string that should only be entered in the table if it is still this solution I I have come up with:

• Output: ua_id

>

  URI strings (ua_hash, u Enter the values ​​(UNHX (MD5 ('Test'), 'Exam'; SELECT ua_id FROM ua_strings WHERE ua_hash = UNHEX (MD5 ('test'));  

< Li> Is it possible to create a query with those two questions?
• How can I improve my table structure or questions?

Most The important thing is that get rid of INSERT IGNORE . I came to know that this primary key will also grow, even if it is unsuccessful. You can burn it very quickly through your 4 billion key. First, do SELECT , this is the most common case.

My first thought is that the argument is in the database function. Be given This gives you all the benefits of encapsulation so how can you work it later.

To get rid of my second hash. It is effectively replacing an index on the UA. Since you only need to check the equivalence to be demonstrative, a hash index will be ideal, but they are not supported in most MySQL table formats.

Instead, I use an index first 255 bytes of user-agent, which should be a lot for MySQL for my work. It also gives you the benefit of a complete index, which you need to do more than simple.

  If not, then if the UISTrint (UAIIDID) is unacceptable, the primary key is the authentication, UA text, key (Ua (255)); The  

function will look like this (note, I'm not the best to write mysql function).

  DELIMITER // CREATE FUNCTION get_ua_id (ua_string text)) Return Entrepreneur Launches Retired; SELECT IU_ID again in the SELECT ua_id WHERE ua = ua_string; / * It is not in the table, it has been added to the table * / case when ROW_COUNT () = 0 is included in the uestring (ua) values ​​(ua_ string); SELECT LAST_INSERT_ID (); Start end; End case; Return returns; END // DELIMITER;  

A function with a hash will look very similar. Hide implementation details and two benchmarks in the function

And actually do not use MD5. It is not affecting performance to use SHA1, you can skip additional 4 bytes per entry, and this hidden problems down the road Using MD5 is like saying "Although there are better locks in every way, I will use this dirty lock because I do not think the door is important now ". You are not a security expert (neither I am) and do not know which parts are important and what not. Just put decent locks on everything. If SHA1 has some major performance issues, you can always change it thanks to the encapsulation of the function.

What is the benchmarking result, I am ready to confirm the profiling, your choice will be revealed which is also a part of the effect on system performance. Go with simple and more flexible indexed options, customize it if it later turns into a problem.