0

Say I have these rows in one UNIQUE column:

  • 202101XXXXX
  • 202101XXXXX
  • 202102XXXXX
  • 202102XXXXX
  • 202103XXXXX
  • 202103XXXXX

The first 6 digits are year and month (YYYYMM), and the XXXXX part are random digits. They are UNIQUE. Let's say there are millions of rows like these.

I need to lookup a certain row e.g., 20210312345, is there a way I could tell MySQL to not look at the whole table and just search on rows with the 202103 prefix? And, if that was possible, would it actually speed up the query?

4
  • If you index your entire field, then searching by the first n characters (s.t. n <= 10) should be sargable! No requirement for a GENERATED column unless you might want to filter on your XXXXX suffix? You'll have a maximum of 99,999 possible suffixes for all your data for XXXXX to be UNIQUE - or do you only mean UNIQUE within a given year/month combination? – Vérace Jan 28 at 16:16
  • @Vérace The whole column contains 202101XXXXX which is "indexed" as UNIQUE. That said, I don't need to do anything anymore since it's "sargable"? – IMB Jan 28 at 17:19
  • So, you can have 201005ABCDEF and 201006ABCDEF? Sargable means that the RDBMS can take advantage of an index on that field if you use LIKE '2010%' - i.e. if the suffix is searched, that's find, but if it's one of the XXXXX's, then you have put an index on a GENERATED` column. – Vérace Jan 28 at 19:08
  • @Vérace Yes you can have those values. – IMB Jan 28 at 19:19
0

This is how indexing essentially works. If you create an index on that column, the data will be sorted by the value, which effectively is on the prefix first and then the suffix.

The other thing you can do is either store only the suffix part (the XXXXX) of the value in it's own column too, either from the application that loads the data or using a Stored Generated Column and then create the index on that field. Doing that allows you to do an exact match search on a more unique value.

3
  • On the first part of your answer, since the column is already UNIQUE, safe to say MySQL already does what I describe? Note the whole item 20210312345 is currently in just one column that is UNIQUE. – IMB Jan 28 at 12:49
  • 1
    @IMB - A UNIQUE is an INDEX. So, what you have is perfectly good already. There would be no noticeable speed advantage if you could skip over the prefix. – Rick James Feb 2 at 5:08
  • @RickJames Got it thanks – IMB Feb 2 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.