字符串到值比较优化MySQL查询 [英] String to Value compare Optimizing MySQL Query

问题描述

我的问题如下: 我有两个相同长度的数组$first和$second，包含字符串.在名为Fullhandvalues的表中，每个字符串都被赋予正值:

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)

我想计算$ first [$ i]比$ second [$ i]具有更好的值多少次，它们具有相同的值多少次以及$ first [$ i]有多少次更差的价值值大于$ second [$ i].

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].

我现在要做的是通过

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

，然后比较这些值.但这似乎很慢(对于表中5000个和50000个条目的数组长度，大约需要6秒)

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)

非常感谢

我如何遍历它们:

$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++;
    }

推荐答案

您的问题是where_in.基本上，您正在建立一个长度为implode(',', $second)的查询(加上更改).这必须首先由Laravel(PHP)生成，然后由您的DBMS分析.

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.

生成的查询还将使用IN(...)表达式，在MySQL中这很慢.

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

在没有有关该应用程序以及如何选择板ID的进一步信息的情况下，您可以使用以下选项:

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

• 创建一个临时表并用您的数组数据填充它(这应该是非常快的，但是最好是该数据应该已经在数据库中了)
• 不要忘记在临时表上创建索引.
• 使用内部join选择.

• 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.

这篇关于字符串到值比较优化MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！