In my laravel project I have a students table which contains the students’ exam results, each student belongs to a school, district and city. All the students can have the initial exam results and some of them fail specific subjects and they have a re exam in those subjects (termed as second_subject or second_total in the database)
At first the data was split into a students and a results table, however, they were all moved to a single students table for performance reasons. There are 16 subjects in total. and also exists a section field, each section have different subjects but there are also subjects in common, when ranking the student on total, schools, districts and cities the ranking should be also partitioned by the section. However the subject_ranking should include everyone who took that subject
public function up()
{
Schema::create((new Student)->getTable(), function (Blueprint $table) {
$table->unsignedBigInteger('seat_number')->primary();
$table->string('name');
$table->foreignIdFor(School::class)->constrained((new School)->getTable());
$table->string('school_name');
$table->foreignIdFor(District::class)->constrained((new District)->getTable());
$table->string('district_name');
$table->foreignIdFor(City::class)->constrained((new City)->getTable());
$table->string('city_name');
$table->unsignedSmallInteger('section');
$this->result($table);
$this->result($table, 'second_');
$table->timestamps();
});
}
public function result(Blueprint $table, string $prefix = ''): void
{
$table->unsignedInteger($prefix . 'school_rank')->nullable();
$table->unsignedInteger($prefix . 'district_rank')->nullable();
$table->unsignedInteger($prefix . 'city_rank')->nullable();
$table->unsignedInteger($prefix . 'country_rank')->nullable();
$table->unsignedFloat($prefix . 'total')->nullable();
$table->unsignedFloat($prefix . 'percentage')->nullable();
foreach (Student::$subjects as $subject) {
$table->unsignedFloat($prefix . $subject)->nullable();
$table->unsignedInteger($prefix . $subject . '_rank')->nullable();
}
$table->unsignedInteger($prefix . 'status')->nullable();
}
in mysql that would be
CREATE TABLE `students` (
`seat_number` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`school_id` bigint(20) unsigned NOT NULL,
`school_name` varchar(255) NOT NULL,
`district_id` bigint(20) unsigned NOT NULL,
`district_name` varchar(255) NOT NULL,
`city_id` bigint(20) unsigned NOT NULL,
`city_name` varchar(255) NOT NULL,
`section` smallint(5) unsigned NOT NULL,
`school_rank` int(10) unsigned DEFAULT NULL,
`district_rank` int(10) unsigned DEFAULT NULL,
`city_rank` int(10) unsigned DEFAULT NULL,
`country_rank` int(10) unsigned DEFAULT NULL,
`total` double(8,2) unsigned DEFAULT NULL,
`percentage` double(8,2) unsigned DEFAULT NULL,
`english` double(8,2) unsigned DEFAULT NULL,
`english_rank` int(10) unsigned DEFAULT NULL,
`math` double(8,2) unsigned DEFAULT NULL,
`math_rank` int(10) unsigned DEFAULT NULL,
-- ... rest of the 16 subjects
`status` int(10) unsigned DEFAULT NULL,
`second_school_rank` int(10) unsigned DEFAULT NULL,
`second_district_rank` int(10) unsigned DEFAULT NULL,
`second_city_rank` int(10) unsigned DEFAULT NULL,
`second_country_rank` int(10) unsigned DEFAULT NULL,
`second_total` double(8,2) unsigned DEFAULT NULL,
`second_percentage` double(8,2) unsigned DEFAULT NULL,
`second_english` double(8,2) unsigned DEFAULT NULL,
`second_english_rank` int(10) unsigned DEFAULT NULL,
`second_math` double(8,2) unsigned DEFAULT NULL,
`second_math_rank` int(10) unsigned DEFAULT NULL,
-- ... rest of the 16 subjects
`second_status` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`seat_number`),
Essentially this data never changes and after importing all the data, the only missing fields are the “rank” fields which we need to do ourselves, which is done using a console command.
public function handle(): void
{
$time = microtime(true);
$this->updateRanksForStudentsWithoutSecondExam();
$secondExists = Student::query()->whereNotNull('second_total')->exists();
if ($secondExists) {
$this->updateRanksForStudentsWithSecondExam();
$this->updatePreviousRanksForStudentsWithSecondExam();
}
$time = number_format(microtime(true) - $time, 2);
$this->info('Updated all ranks in ' . $time . ' seconds');
}
/**
* Update the ranks for students who didn't attend any second exam based on the latest results they have
*
* @return void
*/
public function updateRanksForStudentsWithoutSecondExam(): void
{
$time = microtime(true);
$this->info('Updating normal ranks for students...');
$subjectsUpdates = [];
foreach (Student::$subjects as $subject) {
$subjectsUpdates["$subject" . '_rank'] = DB::raw("case when $subject is null then null else {$subject}_rank_calculated end");
}
$count = Student::query()->toBase()
->whereNull('second_total')
->joinSub($this->getCoalescedRanksQuery(), 'ranks', 'seat_number', '=', 'seat_number_calculated')
->update([
'country_rank' => DB::raw('country_rank_calculated'),
'school_rank' => DB::raw('school_rank_calculated'),
'district_rank' => DB::raw('district_rank_calculated'),
'city_rank' => DB::raw('city_rank_calculated'),
...$subjectsUpdates,
]);
$time = number_format(microtime(true) - $time, 2);
$this->info('Updated the ranks of ' . $count . ' students without second exam in ' . $time . ' seconds');
}
/**
* Essentially the same as the previous method, but for students who have a second exam
* since now we are updating the second_ fields instead of the normal fields
*
* @return void
*/
public function updateRanksForStudentsWithSecondExam(): void
{
$time = microtime(true);
$this->info('Updating second exam ranks for students...');
$updateSubjectsRanksRaw = [];
foreach (Student::$subjects as $subject) {
$updateSubjectsRanksRaw['second_' . $subject . '_rank'] = DB::raw("case when $subject is null then null else {$subject}_rank_calculated end");
}
$count = Student::query()->toBase()
->whereNotNull('second_total')
->joinSub($this->getCoalescedRanksQuery(), 'ranks', 'seat_number', '=', 'seat_number_calculated')
->update([
'second_country_rank' => DB::raw('country_rank_calculated'),
'second_school_rank' => DB::raw('school_rank_calculated'),
'second_district_rank' => DB::raw('district_rank_calculated'),
'second_city_rank' => DB::raw('city_rank_calculated'),
...$updateSubjectsRanksRaw,
]);
$time = number_format(microtime(true) - $time, 2);
$this->info('Updated the ranks of ' . $count . ' students with second exam in ' . $time . ' seconds');
}
/**
* This will update the initial ranks of the students who had a second exam based only on the initial results of all the students
* this is so the student can compare their ranks before and after the retaking the exam
*
* @return void
*/
public function updatePreviousRanksForStudentsWithSecondExam(): void
{
$time = microtime(true);
$this->info('Updating previous ranks for students with second exam...');
$updateSubjectsRanksRaw = [];
foreach (Student::$subjects as $subject) {
$updateSubjectsRanksRaw["$subject" . '_rank'] = DB::raw("case when $subject is null then null else {$subject}_rank_calculated end");
}
$count = Student::query()->toBase()
->whereNotNull('second_total')
->joinSub($this->getNonCoalescedRanksQuery(), 'ranks', 'seat_number', '=', 'seat_number_calculated')
->update([
'country_rank' => DB::raw('country_rank_calculated'),
'school_rank' => DB::raw('school_rank_calculated'),
'district_rank' => DB::raw('district_rank_calculated'),
'city_rank' => DB::raw('city_rank_calculated'),
...$updateSubjectsRanksRaw,
]);
$time = number_format(microtime(true) - $time, 2);
$this->info('Updated previous ranks of ' . $count . ' students in ' . $time . ' seconds');
}
/**
* This returns a subquery that contains all the students ranked based on their latest result (either the second, or the first if there is no second)
*
* @return Builder
*/
public function getCoalescedRanksQuery(): Builder
{
$selects = [];
foreach (Student::$subjects as $subject) {
$selects[] = DB::raw("rank() over (order by COALESCE(second_$subject, $subject) desc) as {$subject}_rank_calculated");
}
foreach (['school', 'district', 'city'] as $type) {
$selects[] = DB::raw("rank() over (partition by section, {$type}_id order by COALESCE(second_total, total) desc) as {$type}_rank_calculated");
}
$selects[] = DB::raw('rank() over (partition by section order by COALESCE(second_total, total) desc) as country_rank_calculated');
return Student::query()->toBase()
->select([DB::raw('seat_number as seat_number_calculated'), ...$selects]);
}
public function getNonCoalescedRanksQuery(): Builder
{
$selects = [];
foreach (Student::$subjects as $subject) {
$selects[] = DB::raw("rank() over (order by $subject desc) as {$subject}_rank_calculated");
}
foreach (['school', 'district', 'city'] as $type) {
$selects[] = DB::raw("rank() over (partition by section, {$type}_id order by total desc) as {$type}_rank_calculated");
}
$selects[] = DB::raw('rank() over (partition by section order by total desc) as country_rank_calculated');
return Student::query()->toBase()
->whereNotNull('total')
->select([DB::raw('seat_number as seat_number_calculated'), ...$selects]);
}
I went through alot of different approaches for calculating and setting these ranks correctly, however since the table is usually 1,000,000+ records (which could be considered alot or not depending on who you ask), this console command takes more than 15 minutes to finish, roughly the first 2 queries takes the same time and third is a tiny bit faster.
Is mysql efficient in calculating the ranks and then setting those ranks based on a subquery? since the data doesn’t change will creating an index on all of those fields be beneficial in faster sorting for ranking? Or would somehow doing all this proccessing in php be faster? I can load the entire table in memory because it is less than 500mb anyways, however I believe this would make the updating part alot slower even if the ranking is faster. any ideas on how to improve this would be appreciated. Storage and import time don’t matter at all, the only thing that matters in this project is speed and performance in all other parts.