Improve performance of query using like

[808]

I'm working with an existing DB table and I'm not sure if my query is just wildly inefficient or if the table is structured poorly. The table has rows for students in classes with a column for "units". The units column has comma separated values. For simplicity, lets say the values for units are the letters of the alphabet. Each row, can have 1 or more letters in any combination, but only one of any letter at a time.

To figure out how many classes have each type of unit (letters of the alphabet), I created an array called $units_array that consists of all the units and the used it in the following:

Code: Select all

foreach ($units_array as $unit) {
	$result=mysqli_query($dbi, "SELECT COUNT(DISTINCT(class_id)) AS num FROM district_data WHERE user_type = 'student' AND class_id != '' AND class_units LIKE '%$unit%'");

		if(mysqli_num_rows($result) > 0) {
			$row=mysqli_fetch_array($result);
			$count=$row['num'];
			$rows.='
			<tr>
				<td>'.$unit.'</td>
				<td>'.$count.'</td>
			</tr>
			';
		}
}

I suspect one the issue is the way this DB is constructed. If a class has 20 students, then each row for those 20 students will have the same list of units. This is why I'm counting distinct class_id.

Given all of this, I'm wondering if my query can be improved to significantly improve performance or if the structure of the DB is the primary culprit.

[Benjamin]

There are a lot of different things you could do here. As is, unless your experiencing issues, they are most likely things you can circle back to. Are you having any specific issues you need to resolve?