1

I have a php script that will typically retrieve around 200 records in a single MySQL query (Database A) and, as it processes each record returned by the query, it needs to look up a value in another database (Database B) - typically 500 records.
Which is the best way to do this ? - either a separate query to Database B for each fetched record in the Database A query loop or to pull all the values from Database B into an array with a single query and then read the required value from the array in the row fetching loop. Or is there any better way ? Any guidance here much appreciated. Thanks.

peter300
  • 75
  • 9
  • Does separate database mean separate mysql instance as well, or are the 2 databases hosted by the same mysql instance? – Shadow May 29 '17 at 12:18
  • Reading them at once (single query) should be faster. – Vatev May 29 '17 at 12:22
  • Have a read about the federated storage engine (https://dev.mysql.com/doc/refman/5.7/en/federated-usagenotes.html) which allows you to have tables linked across databases. – Nigel Ren May 29 '17 at 12:24
  • @NigelRen the federated table engine is to connect tables from different mysql instances, not just from different databases. You do not need federated tables if the databases are hosted in the same instance. – Shadow May 29 '17 at 12:27
  • Until we know where the databases are, I just wanted to add options. – Nigel Ren May 29 '17 at 12:29
  • If the 2 databases are located in different mysql instances, then see the following SO question for answer: https://stackoverflow.com/questions/11114197/join-tables-from-two-different-server – Shadow May 29 '17 at 12:31
  • Although if you read the title - it does say 'on different servers'. – Nigel Ren May 29 '17 at 12:31
  • @NigelRen and if you read the body of the question, it says different databases. I provided duplicate links for both versions. – Shadow May 29 '17 at 12:32
  • Thanks for the input. Thanks Vatev, that was my first thought. I'm looking at Shadow's link for different MySQL instances. – peter300 May 29 '17 at 12:46

1 Answers1

-1

I would read first all records from B into an array.

Next read the records from A and perform a lookup on the array created from B.

SieGeL
  • 303
  • 2
  • 6