Single MySQL Query one-to-many efficiency

Question

I am fairly inexperienced with the ramifications of doing a single select where joining several tables and there are several one-to-many relationships while also running some built in MySQL functions using column values between tables.

The tables will be fairly large: 10,000 records in the accounts table, 70,0000 records in the locations table, 350,000 in the email_sequences table.

The select I am performing: (from Laravel's query builder)

$interval = $getSheduleInterval($current);
$sql = "SELECT seq.id FROM locations AS loc
 JOIN accounts as acc ON acc.id=loc.account_id
 JOIN email_sequence AS seq ON loc.id=seq.location_id
 WHERE acc.suspended = 0
 AND acc.deleted_at IS NULL
 AND loc.active = 1 
 AND seq.immediate = 0
 AND TIME(CONVERT_TZ(seq.schedule_time, 'UTC', loc.timezone)) >= {$interval->start}
 AND TIME(CONVERT_TZ(seq.schedule_time, 'UTC', loc.timezone)) < {$interval->end}";
$sequents = DB::select($sql);

I have seen (read) where in some instances it is more efficient to perform several queries and process the result of each in between rather than a complex SELECT query where several one-to-many or many-to-one relationships are involved.

Are there going to be any major issues with this single select as I have it currently constructed?

Edit: As requested here is the $getScheduleInterval() code;

// @param object [Takes a instance of Carbon::now()] 
public function getScheduleInterval($carbonObj){
 $interval = (object)[];
 // round time down to quarter hour
 $carbonObj->minute = $carbonObj->minute - ($carbonObj->minute % 15);
 $carbonObj->second = 00;
 //set start / end
 $interval->start = "'{$carbonObj->toTimeString()}'";
 $interval->end = "'{$carbonObj->addMinutes(15)->toTimeString()}'";
 return $interval;
}

• Sᴀᴍ Onᴇᴌᴀ: where is $interval defined? Is$intervals an array, which is then traversed in a loop, wherein $interval is the iterator variable?
• skribe: The $interval is a standard object. $interval->start the current time rounded down to nearest quarter hour. $interval->end current time plus 15 minutes. e.g. 12:00:00 12:15:00. It does not change for the entire query nor in a loop.
• Sᴀᴍ Onᴇᴌᴀ: Thanks for that information, however it still isn't clear where $interval is defined... Please edit your post to include a definition/assignment of $interval. If I was to try to run that code (presuming I had the DB configured), I would most likely receive an error stating that $interval was undefined. Perhaps having a definition for the function at $getSheduleIntervals and $current would also be helpful.
• skribe: I don't feel like it is all that relevant, but I appreciate the help, so I have added the code that calculates the $interval
• skribe: AH! Now I see some how I have a typo and that is why you were asking where 'interval' was defined. The $intervals at the top should be simply $interval. My greatest apologies.

Answer 1

I have seen (read) where in some instances it is more efficient to perform several queries and process the result of each in between rather than a complex SELECT query where several one-to-many or many-to-one relationships are involved.

Does that mean running a query to select data from each table and then subsequent queries to select the affiliated data? If so, repeated (redundant) queries (i.e. on one table where only a WHERE condition changes) can lead to poor performance. But, if the associated data can be shared across records (e.g. multiple accounts have the same location) then it might be better to have one query per table and then look up records in various result sets when needed.

If paging can be integrated, for example: populate the first \$n\$ results, where \$n\$ can be 20, 40, 60, etc. then the query with the joined tables might be optimal.

Are there going to be any major issues with this single select as I have it currently constructed?

The query as it is above might likely take a while, given the high number of records in each table. But as mentioned above, paging would likely improve the situation greatly.

• skribe: Here is one article by Jeff Atwood blog.codinghorror.com/all-abstractions-are-failed-abstractions It is a fairly different situation but I was wondering if the same principles might apply. --- Also I don't see how the queries could be considered 'redundant.' It would be doing one query to get active accounts, another for active locations, then processing those and making additional queries base on the results etc.
• Sᴀᴍ Onᴇᴌᴀ: okay - I should have been more clear: by redundant queries I meant queries to one table where only a WHERE condition changes. I have updated the explanation.