在mysql WHERE子句中正确传递5,000到100,000个值的方法

Question

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:

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

Queries :

    $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();

I am using symfony 2.0 framework , doctrine 2.0

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,

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 ?

Answer 1

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