MySQL: optimized query to find matching rows from a rowset

Question

I have 10 sets of rows, each of which has 9 rows. Of these 10 sets, all rows in the first set have a length of 10, and in the second - a length of 9, and so on. Finally, all rows in the 10th set are 1 in length.

Each set has a common prefix (length-2) characters. And the prefix length is reduced by 1 in the next set. Thus, the first set has 8 characters, the second has 7, etc.

Here is an example that looks like a selection of 10 sets:

pu3q0k0vwn
pu3q0k0vwp
pu3q0k0vwr
pu3q0k0vwq
pu3q0k0vwm
pu3q0k0vwj
pu3q0k0vtv
pu3q0k0vty
pu3q0k0vtz

pu3q0k0vw
pu3q0k0vy
pu3q0k0vz
pu3q0k0vx
pu3q0k0vr
pu3q0k0vq
pu3q0k0vm
pu3q0k0vt
pu3q0k0vv

pu3q0k0v
pu3q0k0y
pu3q0k1n
pu3q0k1j
pu3q0k1h
pu3q0k0u
pu3q0k0s
pu3q0k0t
pu3q0k0w

pu3q0k0
pu3q0k2
pu3q0k3
pu3q0k1
pu3q07c
pu3q07b
pu3q05z
pu3q0hp
pu3q0hr

pu3q0k
pu3q0m
pu3q0t
pu3q0s
pu3q0e
pu3q07
pu3q05
pu3q0h
pu3q0j

pu3q0
pu3q2
pu3q3
pu3q1
pu3mc
pu3mb
pu3jz
pu3np
pu3nr

pu3q
pu3r
pu3x
pu3w
pu3t
pu3m
pu3j
pu3n
pu3p

pu3
pu9
pud
pu6
pu4
pu1
pu0
pu2
pu8

pu
pv
0j
0h
05
pg
pe
ps
pt

p
r
2
0
b
z
y
n
q

Requirements: I have a PROFILES table with columns SRNO (type bigint, primary key) and UNIQUESTRING (type char (10), unique key). I want to find 450 SRNOs for matching UNIQUESTRING from these 10 sets.

, . (, 450), , . (450 ), , . .

: :

select srno from  profiles
    where  ( (uniquestring like 'pu3q0k0vwn%')
              or  (uniquestring like 'pu3q0k0vwp%') -- all those above uniquestrings after this and finally the last one
              or  (uniquestring like 'n%')
              or  (uniquestring like 'q%')
           )
    limit  450

, , , , , . :

(select srno from  profiles where uniquestring like 'pu3q0k0vwn%' LIMIT 450)
UNION DISTINCT
(select srno from  profiles where uniquestring like 'pu3q0k0vwp%' LIMIT 450); -- and more such clauses after this for each uniquestring 

, .

Answer 1

SELECT ...
    WHERE str   LIKE  'pu3q0k0vw%' AND -- the 10-char set
          str REGEXP '^pu3q0k0vw[nprqmj]'  -- the 9 next letters
    LIMIT ...
# then check for 450; if not enough, continue...
SELECT ...
    WHERE str   LIKE  'pu3q0k0vt%' AND -- the 10-char set
          str REGEXP '^pu3q0k0vt[vyz]'  -- the 9 next letters
    LIMIT 450
# then check for 450; if not enough, continue...
etc.
SELECT ...
    WHERE str   LIKE  'pu3q0k0v%' AND -- the 9-char set
          str REGEXP '^pu3q0k0v[wyzxrqmtv]'  -- the 9 next letters
    LIMIT ...
# check, etc; for a total of 10 SELECTs or 450 rows, whichever comes first.

10+. LIKE, REGEXP.

( pu3q0k0vw vs. pu3q0k0vt, .)

""; LIKE REGEXP, .

UNION , ( ) 450. SELECT LIMIT, DISTINCT GROUP BY ORDER BY .

REGEXP , ; LIKE ( , 20% LIKE).