Improve performance of query using like

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
808
Forum Newbie
Posts: 1
Joined: Sun Mar 14, 2021 10:15 am

Improve performance of query using like

Post by 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.
User avatar
Benjamin
Site Administrator
Posts: 6909
Joined: Sun May 19, 2002 10:24 pm

Re: Improve performance of query using like

Post by 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?
Post Reply