trjhtr

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;

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.