Unanswered: Need help to optimize SELECT query and table
I have a table that consists of one column only, character(81). In this table I will have over 5 billion rows.
I will then need to apply a boolean pattern to each row. In this pattern, if a bit is true the character at the same place is kept, but if the bit is false another character will be returned, for instance '0'. The same character needs to be returned for every false bit.
Let me give an example. In this pattern '.' means true, and ' ' means false.
Pattern".. . "
The rows in the database shouldn't be changed but returned according to the pattern.
When that has been done, only those rows that are unique, i.e. have COUNT(*) = 1, should actually be returned.
And as I need to do the above steps for hundreds of thousands of patterns, I really need to focus on optimizing. And this is where I can use all the advice I can get.
I HAVE been thinking of doing the pattern matching with regular expressions, but I have not been able to find a way to use boolean matching with regex.