在mysql WHERE子句中传递5,000到100,000个值的正确方法 [英] Correct way to pass between 5,000 to 100,000 values in mysql WHERE clause

问题描述

我从一个查询结果中获取45000个值，并且需要使用该值进行第二个查询，但是由于数组的大小较大，执行该方法所花费的时间超过30秒，因此出现错误:

I am getting 45000 values from one query result, and need to use this values to make second query but because of large size of array, its taking long time more than 30 seconds to execute so getting Error:

错误:最长执行时间超过30秒

Error: Maximum execution time of 30 seconds exceeded

是否有其他方法可以快速进行此数据库数据计算，或者我应该计算数据并将其保存在另一个表中以随时显示

Is there any other way to do this database data calculation quickly, or i should calculate the data and save it in another table to show at anytime

查询:

    $query = $em->createQuery('SELECT DISTINCT(u.username) as mobile FROM sessionTable u WHERE u.accesspoint IN (?1) ');
    $query->setParameter(1, $accesspoint);
    $result = $query->getResult();
    $count = count($result);
    $i = 0;
    $numbers = array();
    foreach ($result as $value) {
        $numbers[$i] = $value['mobile'];
        $i++;
    }

    dump(count($numbers)); //---->  Output is 48567 --successful

    $Users = $this->getDoctrine()
            ->getRepository('AcmeDataBundle:User')
            ->findByNumber($numbers);

    ----Error Occurs Here------

    dump(count($Users));
    die();

我正在使用symfony 2.0框架，教义2.0

I am using symfony 2.0 framework , doctrine 2.0

更新: 考虑我在同一数据库中有5个表， 即1个用户2个-googleData 3- facebook数据4个yahooData 5个会话

UPDATE : consider I have 5 tables in same database, viz. 1- user 2- googleData 3- facebook data 4-yahooData 5- sessions

当用户登录我的应用程序时，我从一个或多个社交资料数据中收集性别信息，并将其保存在该特定表格中

when users login to my application, I collect there gender info from either one or multiple social profile data, and save in that particular table

现在，我要计算使用过多次会话的所有男性:女性比率，

Now I want to calculate all users male:female ratio who have used multiple sessions,

在这种情况下，很难从多个表中计算出男性与女性的比例. 我觉得有一种解决方案，如果我直接在会话表中添加Gender列会很容易，但是通过使用FK或其他方法还有其他更好的方法吗?

For this scenario its going too tough to calculate male :female ratio from multiple tables. I feel there is one solution it will be easy if I adds Gender column directly in session table but is there any other better way by using FK or anything else ?

推荐答案

如果必须传递1000个值，则不要介意100,000个值，这可能意味着您在设计查询时遇到了问题.数据必须来自某个地方，如果它来自数据库，那么使用联接或子查询将是一件简单的事情.如果来自外部，则可以进入临时表.

If you are having to pass 1000 values never mind 100,000 values, that probably means you have a problem in the design of your queries. The data has to come from somewhere, if it's from the database, it would be a simple matter to use a join or a sub query. If it's from an external source, it can go into a temporary table.

简而言之:是的，最好使用临时表或永久表

so in short: Yes, a temporary or permanent table is better

这篇关于在mysql WHERE子句中传递5,000到100,000个值的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！