Query in slow mysql

Question

Query in slow mysql

Navigation

#1 by (1 votes)

0

I have this query in mysql:

SELECT MIN('Dc3') +1 as prox_ean_libre FROM (SELECT 'Dc3' FROM 'Test' WHERE 'Dc3' BETWEEN '".$Eanmin."' AND '".$Eanmax."') t1 WHERE not exists (select null FROM 'Test' t2 WHERE t2.'Dc3' = t1.'Dc3' + 1 AND t2.'Dc3' BETWEEN '".$Eanmin."' AND '".$Eanmax."'

In it I look for the first free code between two numbers. the fact is that it works but it is extremely slow when mysql has many records. Is there any way to make it faster?

Eanmin is 8434452000001 and Eanmax 8434452099999 Dc3 is a field varchar 200

mysql php

asked by Killpe 12.10.2017 в 20:16

source

1 answer

Obtain unrelated data from each other Find Perimeters of a circumference

score 1 · Answer 1

You are using two subqueries when you could use a single query

SELECT MIN('Dc3')+1 as prox_ean_libre
FROM 'Test' t1
LEFT JOIN 'Test' t2 ON t2.'Dc3'=(t1.'Dc3')+1 AND t2.'Dc3' BETWEEN '".$Eanmin."' AND '".$Eanmax."'
WHERE t1.'Dc3'BETWEEN '".$Eanmin."' AND '".$Eanmax."'
AND t2.'Dc3' IS NULL

In parentheses, your query is interpolating the value of the variables in the same statement. Without further context I do not know how to recommend changing it, but you should use placeholders for the variables $Eanmin and $Eanmax to leave, for example (in PDO)

$sql = "SELECT MIN('Dc3')+1 as prox_ean_libre
    FROM 'Test' t1
    LEFT JOIN 'Test' t2 ON t2.'Dc3'=(t1.'Dc3')+1 AND t2.'Dc3' BETWEEN :eanmin AND :eanmax
    WHERE t1.'Dc3'BETWEEN :eanmin AND :eanmax
    AND t2.'Dc3' IS NULL";

$stmt = $conn->prepare($sql);
$stmt->execute([
  ':eanmin' => $Eanmin,
  ':eanmax' => $Eanmax
]);

PS1 : I suppose you have an index in Dc3