CODEWITHSUNDEEP
phpmysqlsql-serverregex
shalomabitan
shalomabitan

Reputation: 175

search MySQL matching n numbers from string of m numbers

I have about 5 million rows in a MySQL database that match the following system

n1-n2-n3-n4-n5 (e.g. 8-23-43-12-3) where each number is unique to that sequence. In addition, each number ranges from 1 to 99.

I need a way to retrieve all rows within the MySQL DB where three out of the five numbers are matched. For example, a user enters 4-23-65-82-3, I want to return all of the rows that share any 3 of the five numbers entered, so 4-65-12-49-82 matches, 34-23-76-3-65 matches, etc.

My table is as follows:

number_table (id , uuid , five_numbers , first_number , second_number , third_number , fourth_number , fifth_number)

What I have tried so far:

  1. loop through every row and match for any row that has the first_number be any of the five numbers the user submitted. This is just not smart!

  2. matching with RegExp in MySQL, but that would require a SQL statement that includes every possible combination. That is not too smart, however if anyone has a more efficient MySQL REGEXPthat I can use great.

I am essentially looking for a SQL solution since I don't have to worry about memory exhaustion, SQL execution would be faster, and I would like to place it in a view so I can execute some commands on that.

I apologize for the lengthy post, but I wanted to give as much info.

Thanks!

Upvotes: 0

Views: 114

Answers (3)

Steve K
Steve K

Reputation: 4921

The MySQL FIELD function is useful here:

SELECT id, uuid, five_numbers, 
FIELD(A, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as aFound,
FIELD(B, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as bFound,
FIELD(C, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as cFound,
FIELD(D, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as dFound,
FIELD(E, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as eFound
FROM number_table
WHERE
(
first_number in (A, B, C, D, E)
OR second_number in (A, B, C, D, E)
OR third_number in (A, B, C, D, E)
)
AND aFound + bFound + cFound + dFound + eFound >= 3;

The FIELD function returns the index of the argument in which it finds the first argument, or it returns 0. If you SELECT (n > 0) as an argument, it resolves to TRUE which MySQL treats as an integer value of 1. So you can add them together to check the values, without having to do a subquery. You can get a little extra performance out of doing the 'in' checks on the individual fields so you don't have to calculate the function values against the whole table rowset. You only have to check the first three fields, though, because if it hasn't matched at least one of them, it can't possibly match three of the five numbers.

It should also possible to simply select 

SELECT * 
FROM number_table
WHERE
((LOCATE(A, five_numbers) > 0) +
(LOCATE(B, five_numbers) > 0) +
(LOCATE(C, five_numbers) > 0) +
(LOCATE(D, five_numbers) > 0) +
(LOCATE(E, five_numbers) > 0)) >= 3

Upvotes: 0

Rana Kolta
Rana Kolta

Reputation: 71

Assuming you have your input from the user as n1, n2, n3, n4, n5 you can write an sql as follows:

select id, uuid, five_numbers from
    (select *, 
        if(first_number in (n1,n2,n3,n4,n5), 1, 0)  +
        if(second_number in (n1,n2,n3,n4,n5), 1, 0) +
        if(third_number in (n1,n2,n3,n4,n5), 1, 0)  +
        if(fourth_number in (n1,n2,n3,n4,n5), 1, 0) +
        if(fifth_number in (n1,n2,n3,n4,n5), 1, 0) as total
     from number_table) as t 
where total >= 3

So if any of the five numbers have the value it will add 1 to the total. Then you can filter your rows with numbers that contain 3 or more numbers of the users input

Upvotes: 1

Alex
Alex

Reputation: 17289

Quick answer is:

SELECT * FROM 
(SELECT *,
 five_numbers REGEXP '^3-|-3-|-3$' as n1,
 five_numbers REGEXP '65' as n2,
 five_numbers REGEXP '82' as n3 
FROM `number_table`) as t 
WHERE t.n1+t.n2+t.n3=3

if you need I can set php code with PDO, just ask

Upvotes: 0

Related Questions