Refactoring #3: Optimizing Eloquent queries & console commands

Optimizing Eloquent queries & console commands

Refactor into smaller methods

There are two main actions that this command does. Firstly, it generates a JSON file with all the latitude & longitude values of litter, hence, the generateFeatures method. Secondly, using this JSON file, a node command (yes, a node command) is executed using the exec PHP function, which generates the clusters into a clusters.json file. Lastly, we populate our clusters database table using the generated JSON. I know, lengthy explanation.

Use cursor() instead of get()

The memory issue with this line Photo::select('lat', 'lon')->get(); is that it will load all the photo objects into memory, and that is a big problem with the 250k+ photos currently in the database. To fix that, Laravel provides a nice query helper called cursor. By using $photos = Photo::select('lat', 'lon')->cursor();, only one Eloquent model is kept in memory at any given time while iterating over the cursor. For that reason, we don't even have to call the unset($photos); method at all.

Use batches instead of individual inserts

Looking at the loop that iterates over all clusters, it’s killing the database with thousands of MySql insert queries for storing the clusters. It would be much better if we utilized the Cluster::insert() method instead of Cluster::create(). This way, we'll only execute one query to insert all the records.

Bonus small improvements

There are three small improvements I’d like to mention. Just to feel… complete.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Geni Jaho

Geni Jaho

Full-stack web developer with a passion for software architecture and programming best practices.