String to Value compare Optimizing MySQL Query

Question

My problem is the following: I have two arrays $first and $second of the same length, containing strings. Every string is given a positive value in a table named Fullhandvalues:

Field: board : string(7) PRIMARY KEY
Field: value : int (11)

I want to count how many times $first[$i] has a better value than $second[$i], how many times they have the same value, and how many times $first[$i] has a worse value than $second[$i].

What I have done now is getting all the values via

$values[0]= DB::table('Fullhandvalues')->where_in("board",$first)->get(Array("value"));
$values[1]= DB::table('Fullhandvalues')->where_in("board",$second)->get(Array("value"));

and then comparing the values. But this seems to be very slow (approximately 6 seconds, for an array length of 5000 and 50000 entries in the table)

Thanks very much in advance

EDIT: How I loop through them:

$win=0;$lose=0;$tie=0;
for($i=0;$i<count($values[0]);$i++)
    {
        if ($values[0][$i]>$values[1][$i])
            $win++;
        elseif ($values[0][$i]<$values[1][$i])
            $lose++;
        else $tie++;
    }

Answer 1

Your problem is where_in. You are basically building a query with the length of implode(',', $second) (plus change). This has to be first generated by Laravel (PHP) and then analysed by your DBMS.

Also the generated query will use the IN(...) expression, which is known to be slow in MySQL.

Without further information about the application and how board IDs are selected, here is an option you have:

• Create a temp-table and fill it with your array data (this should be quite fast, but preferably this data should already be in the database)
• Don't forget to create an index on the temp table.
• Select with an inner join.