I try to import a relatively high number of data in a mysql database (around 6 millions entries coming from text files).

I have to check for each entry if there is not already a similar record in the database by comparing it with two text fields : 

```sql
`ref` varchar(30) COLLATE utf8_unicode_ci NOT NULL
`labelCanonical` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL

```

Files are processed by batches of N entries (for this example 10), and I do a single query to check for all duplicates in the batch, like so :

```sql
SELECT p.`ref`, p.`labelCanonical` 
FROM `rtd_piece` p 
WHERE (p.`ref` = "6569GX" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569GY" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569GZ" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569H0" AND p.`labelCanonical` = "fsc-habitacle") 
  OR (p.`ref` = "6569H1" AND p.`labelCanonical` = "support-fsc") 
  OR (p.`ref` = "6569H2" AND p.`labelCanonical` = "fsc-injection") 
  OR (p.`ref` = "6569H4" AND p.`labelCanonical` = "fsc-injection") 
  OR (p.`ref` = "6569H8" AND p.`labelCanonical` = "faisceau-mot") 
  OR (p.`ref` = "6569H9" AND p.`labelCanonical` = "faisceau-mot") 
  OR (p.`ref` = "6569HA" AND p.`labelCanonical` = "fsc-principal")

```

I use Doctrine 2 (without Symfony), and I do this query using "NativeQuery".

This problem is, even with a 600k entries in the database, this query takes **730ms** *(or 6.7 seconds for a batch of 100 records)* to execute and it increases dramatically as records are added to the database.

I have no index on "ref" or "labelCanonical" fields for now, and I'm not sure if adding one will do any good with the kind of request I do.

Where I am wrong with this method so its so slow ?

***Edit to add more information about the process.***

I do an ajax query for each batch, also to give a feedback to the user.
When in the server side (PHP), I do the following procedure : 

1) I seek on the current file on processing and extract next N records

2) I parse each line and add references and slugified labels to two different arrays

3) I try to get these records from the database to avoid duplicates :

```
$existing = array();
$results = getRepository('Piece')->findExistingPieces($refs, $labels);
for ($i = 0, $c = count($results); $i < $c; ++$i) {
    $existing[] = $results[$i]['ref'].'|'.$results[$i]['labelCanonical'];
}

```

```php
public function findExistingPieces(array $refs, array $labels)
{
    $sql = '';
    $where = array();
    $params = array();

    for ($i = 0, $c = count($refs); $i < $c; ++$i) {
        $params[] = $refs[$i];
        $params[] = $labels[$i];
        $where[] = '(p.`ref` = ? AND p.`labelCanonical` = ?)';
    }

    $sql = 'SELECT p.`ref`, p.`labelCanonical` '.
           'FROM `rtd_piece` p '.
           'WHERE '.implode(' OR ', $where);

    $rsm = new ResultSetMapping;
    $rsm->addScalarResult('ref', 'ref');
    $rsm->addScalarResult('labelCanonical', 'labelCanonical');

    $query = $this->getEntityManager()
                  ->createNativeQuery($sql, $rsm)
                  ->setParameters($params);
    return $query->getScalarResult();
}

```

4) I iterate through previously parsed data and check for duplicates : 

```
for ($i = 0; $i < $nbParsed; ++$i) {
    $data = $parsed[$i];

    if (in_array($data['ref'].'|'.$data['labelCanonical'], $existing)) {
        // ...
        continue ;
    }
    // Add record
    $piece = new PieceEntity;
    $piece->setRef($data['ref']);
    //...

    $em->persist($piece);
}

```

5) I flush at the end of the batch

I've added some "profiling" code to track the time being spent for each step, here the result : 

```
0.00024509429931641 (0.245 ms) : Initialized
0.00028896331787109 (0.289 ms) : Start doProcess
0.00033092498779297 (0.331 ms) : Read and parse lines
0.0054769515991211 (5.477 ms) : Check existence in database
6.9432899951935 (6,943.290 ms) : Process parsed data
6.9459540843964 (6,945.954 ms) : Finilize
6.9461529254913 (6,946.153 ms) : End of process
6.9464020729065 (6,946.402 ms) : End doProcess
6.9464418888092 (6,946.442 ms) : Return result

```

The first number show microseconds elapsed since the beginning of the request, then the same time in milliseconds and then what is being done.