Geek Answers Handbook

PHP MySQL for permutations in a MySQL table

I have a mysql table with 7 columns in which each row contains integer values.

I have a simple site that receives values ​​from a user, and I should try to check if the values ​​sent by the user match or look like any of the rows in the table.

Thus, the user writes, for example. 1 2 3 4 5 6 7 as input.

I need to find out if any of the rows in my table look like without specifying the order. So, 1 2 3 4 5 6 7 = 7 6 5 4 3 2 1 and so on. The my table contains more than 40,000 rows of data.

I also need to see if they have at least 5 , 6 or 7 digits.

This means using permutations to find all possible combinations. However, what is the best approach for such a problem?

  • Take user input and get all permutations and map to first line, second line, etc. and let me know if they are found? Alternatively, do the opposite, get the row from the table and get all the permutations and match the user input?

  • What about memory and CPUusage when navigating through such a large table with so many permutations?

Thanks for any advice on this! Souciance

+7
source share
3 answers

In a full normalized schema, this is the only request with the request

Suppose your table with pk as:

 create table T1 ( pk char (1), a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int); insert into T1 values ('a',1,2,3,4,5,6,7), ('b',2,3,4,5,6,7,8), ('z',10,11,12,13,14,15,16);

At this time, we can normalize the data as:

 select pk, case a when 1 then a1 when 2 then a2 when 3 then a3 when 4 then a4 when 5 then a5 when 6 then a6 when 7 then a7 end as v from T1 cross join (select 1 as a from dual union all select 2 as a from dual union all select 3 as a from dual union all select 4 as a from dual union all select 5 as a from dual union all select 6 as a from dual union all select 7 as a from dual ) T2

In the previous request, it is easy to meet your requirements with one:

 select pk from ( select pk, case a when 1 then a1 when 2 then a2 when 3 then a3 when 4 then a4 when 5 then a5 when 6 then a6 when 7 then a7 end as v from T1 cross join (select 1 as a from dual union all select 2 as a from dual union all select 3 as a from dual union all select 4 as a from dual union all select 5 as a from dual union all select 6 as a from dual union all select 7 as a from dual ) T2 ) T where Tv in ( 4,5,6,7,8,9,10) group by pk having <-- The Having count( pk ) > 4

Results :

 | PK | ------ | b |
+3
source

The light method may be to add an extra field to your database, which is a numerical ordered version of all 7 fields.

eg. if the data in the database were 2 4 7 6 5 1 3, the combination field would be 1234567

Then, when comparing, sort the user responses numerically and compare them with the combination field in the database.

Depending on what you are doing, you can write your request as follows

 select * from table where combination like '12%' or combination like '123%'

If you know the minimum number of suitable rooms should be, this will facilitate the request.

To find out how they look like what was in the database. You can use the levenshtein PHP function: http://php.net/manual/en/function.levenshtein.php

 $result = levenshtein($input,$combination);
+1
source

I am afraid that you may not be able to fulfill the request on such a problem so effectively.

You can create a WHERE like:

 (`1` IN ARRAY(1,2,3,4,5,6,7) AND `2` IN ARRAY(1,2,3,4,5,6,7) AND `3` IN ARRAY(1,2,3,4,5,6,7) AND `4` IN ARRAY(1,2,3,4,5,6,7) AND `5` IN ARRAY(1,2,3,4,5,6,7)) OR (`1` IN ARRAY(1,2,3,4,5,6,7) AND `2` IN ARRAY(1,2,3,4,5,6,7) AND `3` IN ARRAY(1,2,3,4,5,6,7) AND `4` IN ARRAY(1,2,3,4,5,6,7) AND `6` IN ARRAY(1,2,3,4,5,6,7)) -- Each combination

But it will be a hell of a condition. Alternatively, you can try using a combination:

First check if column 1 contains information:

 IF( `1` IN ARRAY(1,2,3,4,5,6,7), 1, 0)

Then we summarize all this data:

 SELECT ( IF( `1` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `2` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `3` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `4` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `5` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `6` IN ARRAY(1,2,3,4,5,6,7), 1, 0) + IF( `7` IN ARRAY(1,2,3,4,5,6,7), 1, 0) ) AS `matches_cnt` FROM t1 HAVING `matches_cnt` >= 5

This will be an iteration over all the lines, and the condition will be quite complex (thus the performance of the bed).

You can also try replacing the values ​​with a binary string, for example:

 1,2,7 = 01000011

And then calculate the Hamming distance between the registered record and the database, but this will only reduce the complexity of the condition, but you will need to repeat all the records remain the same.

Implementation in mysql with:

Replace the first part with:

 SELECT ( $MAX_NUMBER$ - BIT_COUNT( XOR( `binary_representation`, $DATA_FROM_USER$)) ) AS `matches_cnt`
0
source

More articles:


All Articles