Hohe Anzahl an Datenbankabfragen vermeiden bzw. Abfrage optimieren

madex

Neuer Benutzer
Beiträge
4
Hallo zusammen,
Ich hab folgendes Problem und ich bräuchte mal ein paar clevere Ideen wie man das anders lösen kann.

Ich habe eine Datenbank mit 180.000 Einträgen.
Und ich habe eine Liste mit 20.000 Barcodes die ich mit der Datenbank abgleichen muss.

Mein Problem ist wenn ich das mit einer simplen foreach-Schleife arbeite ich 20.000 mal diesen Aufruf habe:

foreach($data as $daten)
{

//Datensatz abfragen
$row = mysqli_fetch_array(mysqli_query($con,sprintf('select t.da from tabelle t where t.barcode=%s limit 1', $barc)));

}

Im ungünstigsten Fall dauert ein select ca. 0,3s. da wäre ich bei über 100min Skriptlaufzeit. Das muss doch irgendwie besser gehen.
Indexierung bringt übrigens nichts, dann ist es sogar noch etwas langsamer.

Die Idee wäre vielleicht die notwendigen Daten aus der Datenbank in einen Array zu laden und dann zu verarbeiten. Aber ob das wirklich schneller ist?

Oder kann man die Datenbankabfrage im Vorfeld einschränken, dass man nicht immer alle 180.000 Einträge durchsucht werden, sondern nur die 20.000 im richtigen Status.

Für eure Ideen bin ich sehr dankbar.
 
Werbung:
Du kannst das umstellen auf where t.barcode in (Liste).

Ist die Spalte ein PK? Ist sie unique? Was für ein Status? Da ist nix erkennbar in Deiner derzeitigen Abfrage. Warum ist es mit Index langsamer? Je nach Verteilung der Werte sollte das nicht so sein, wie ändert sich der Plan?
 
Also die Spalte Barcode ist weder PK noch unique. Bisher ist aber jeder Barcode eindeutig.

Ich habe testweise einen Index zu Barcode hinzugefügt und keinen Performancegewinn feststellen können.

Wie ist denn die genaue Syntax dazu? So geht es schonmal nicht:
select t.da from tabelle t where t.barcode=802233 in (select * from tabelle t where t.barcode where t.statusid = 1) limit 1
 
Kurzdemo:

Code:
test=*# create table foo(id serial primary key, val numeric default random());
CREATE TABLE
test=*# insert into foo select * from generate_series(1,10) s;
INSERT 0 10
test=*# select * from foo where id in (1,3,5,6);
 id |  val   
----+-------------------
  1 |  0.56791836488992
  3 | 0.429640639573336
  5 | 0.939905948936939
  6 | 0.406698873732239
(4 rows)

test=*#

Oder was genau willst Du erreichen?
 
Von den 180.000 Einträgen sind nur ca. 20.000 relevant die den Status = 1 haben. Die Idee war es damit zu beschränken. Kann man das nicht über einen Subselect lösen? Ich hab den noch wirklich gebraucht.
Aber die Idee die Barcodes in einer Liste zu laden ist auch eine gute Idee. Vielen Dank.
 
ich hab mal 1 Million Rows in die Tabelle eingetüdelt:

Code:
test=*# insert into foo select * from generate_series(11,1000000) s;
INSERT 0 999990

und suche mal nach 10 davon:

Code:
test=*# explain analyse select * from foo where id in (1000,3000,50000,66666,100000, 200000, 300000, 400000, 900000);
  QUERY PLAN   
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=39.89..74.98 rows=9 width=36) (actual time=0.383..0.414 rows=9 loops=1)
  Recheck Cond: (id = ANY ('{1000,3000,50000,66666,100000,200000,300000,400000,900000}'::integer[]))
  Heap Blocks: exact=9
  ->  Bitmap Index Scan on foo_pkey  (cost=0.00..39.89 rows=9 width=0) (actual time=0.367..0.367 rows=9 loops=1)
  Index Cond: (id = ANY ('{1000,3000,50000,66666,100000,200000,300000,400000,900000}'::integer[]))
 Planning time: 0.114 ms
 Execution time: 0.453 ms
(7 rows)

test=*#

also für 10 Rows 0.453ms, wären hochgerechnet auf 20000 dann 0,9 Sekunden etwa.
 
Also mit index auf Barcode und Status und dieser Abfrage komme ich immerhin auf:

MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze). (Die Abfrage dauerte 0.1260 Sekunden.)
select t.da from tabelle t where t.barcode=802233 and t.status=1
 
Werbung:
Zurück
Oben