Getting data from external mysql db

Hi. I've made a hosting system which has a database of about 5k domains, all very nice. If I just dump that info to a table it takes like 0.5 seconds to load the page, pretty fast. However, foreach one I need to lookup a project id from a seperate mysql db thats on a different server.

So in my code I just do:

foreach(domains as domain){ domain->projectId = (mysql query) }

this adds a lot of time and makes the page take about 5 seconds to load. Can anyone advise a quicker way of doing this? My best idea so far is to make a cron job that runs regularly and just pulls the info from the external DB into my main DB, however I dont like this as I want the data to be really 'live', eg if it is changed in the other db I want it to be immediately visible on my site.

Hope this makes sense any advice appreciated thanks :)

EventFellows

9 years ago

Level 16

I supposte you run into the typical n+1 problem (see documentation)

There are 3 things that come to mind to solve it:

eager loading the relationship but I am not sure if an how that might automatically work in 2 different databases
cache the results from the external DB for an appropriate time and flush the cache on any update on the external DB
manually solve n+1 problem so you only have 2 DB querys (1. get all Domains + 2. get all IDs for all domains)

anarki

9 years ago

Level 1

Hiya yeah indeed thats the problem.

Your last one sounds like a really good solution, just do one query to get everything and then do an array merge or something in php. I'll give that a try thank you!

Please or to participate in this conversation.