Jump to content
Larry Ullman's Book Forums

Recommended Posts

How do I create an index for TINYBLOB?

 

The code: INDEX(tinyblobsection) returns an error saying that a length must be provided...

 

My question is, what number should I set the length to, if I want the entire TINYBLOB column to be indexed?

 

Note: Text inserted into the TINYBLOB column would be encrypted using AES_ENCRYPT, and before encryption the text could not be longer than 150 characters.

Link to comment
Share on other sites

I want to index the TINYBLOB so that I can use it in a WHERE clause: "SELECT * FROM table WHERE email = AES_ENCRYPT('my@ema.il', 'code')" (email column is the TINYBLOB column)

 

Shouldn't the whole TINYBLOB be indexed, so that the entire email is indexed?

Either way, what number do you recommend setting the limit to?

Link to comment
Share on other sites

You don't need to index an entire BLOB or TEXT column. In fact, by restricting the index, you'll get much better performance. What you'd want to do is index whatever X number of characters will reasonably index unique results. With a column like this, I'd think you could get away with indexing the first 10 or 20 characters. You can always change the index later, once you have a sense of the data, too.

Link to comment
Share on other sites

Thank you very much. I'm finally understanding the point of limiting indexes.

 

For example, a table has a column with the TEXT type (moving away from TINYBLOB for now), and it contains rows: Jake, Janet, Bob, Lowry, Jorge

So I should set the index limit to 3, since each row is different after the first 3 characters of each?

 

I'm very grateful for your help!

Link to comment
Share on other sites

  • 3 months later...

I have a further question: Would there be a concern if two rows had the same data within the INDEX length?

 

Example: If a table contains the rows: Jake, Janet, Bob, Lowry, Jarry.

I set the INDEX limit to 2.

Then I queried: SELECT * FROM table WHERE name = 'Jake'

Would this be a problem, since other rows also start with 'Ja'?

 

Thanks again!

Link to comment
Share on other sites

No, not a problem, just not as efficient in that situation as if the index were 3. You've got to set the index length to something that's long enough to be overwhelmingly useful but not so long as to be unnecessarily long (and therefore tedious to maintain).

Link to comment
Share on other sites

 Share

×
×
  • Create New...