# Single MySQL Query one-to-many efficiency

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;

}

• where is $interval defined? Is$intervals an array, which is then traversed in a loop, wherein $interval is the iterator variable? – Sᴀᴍ Onᴇᴌᴀ Jan 26 '18 at 19:17 • 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. – skribe Jan 26 '18 at 19:32
• 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. – Sᴀᴍ Onᴇᴌᴀ Jan 26 '18 at 19:40 • 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 Jan 26 '18 at 19:53
• 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. – skribe Jan 26 '18 at 20:05

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.
• 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. – Sᴀᴍ Onᴇᴌᴀ Jan 26 '18 at 23:21