How to optimize this php function?


#1

Hello, i have function that need to connect to same table 2 times and this check is happening in while loop to know show person or not

function rw3($conn, $worker, $from, $to, $inc, $count){

	$result = mysqli_query($conn, "
	SELECT person 
	FROM car_bussy 
	WHERE person = '".$worker."' 
	AND (date BETWEEN '".$from."' AND '".$to."') 
	AND incident_id='".$inc."' 
	AND count_id!='".$count."'");
	$row = mysqli_fetch_array($result);
	
	$result2 = mysqli_query($conn,"
	SELECT person 
	FROM car_bussy 
	WHERE person = '".$worker."' 
	AND date BETWEEN '".$from."' AND '".$to."' 
	AND (incident_id!='".$inc."' || incident_id='".$inc."' AND count_id!='".$count."')");
	$row2 = mysqli_fetch_array($result2);	

	if (mysqli_num_rows($result2)>0){		
		$person_works='w';
	}else{
		$person_works=null;
	}

	if(($person_works=='') || ($row['person']!=$row2['person'])){
		return 'y';
	}else{
		return 'n';
	}

}

There are 500 persons in each dropdown (3 total dropdowns) and it takes more than 2 seconds to load but if i remove this check it works fast!

I have all good with indexes! Is there anything i can do? I do not see a possible way to merge querys or something :frowning:


#3

are the dates different for those 500 persons?


#4

if you are about $from and $to then for every person its same


#5

Then you could use a prepared statement.

And since the second query contains the first one, a flag for which part of the condition matches would render the first query superfluous.


#6

Can you explain me more? I have no idea where to start!


#7

The only difference between the first and second queries, if I read correctly, is that the second one will also return rows where incident_id != $inc. So you might be able to save time by just running the second query and scanning the results.

Your code seems to presume that the first query will always return at least one row, because you don't check what's in $row prior to using it for comparison towards the end. If that's correct, you could just get the row-count from the second query and subtract one from it, which should give the same result.

Do you call this function individually for the 500-odd people in each drop-down? If you do, I suspect what @Dormilich was suggesting was that a prepared statement would speed this up a little. You'd need to alter the structure of the code a little, so perhaps your calling code would prepare the statement, then pass a pointer to it into this function to execute it with the varying values.

ETA: How does the second condition in this if statement ever do anything?

if(($person_works=='') || ($row['person']!=$row2['person'])){

Both of your queries only select rows where person = $worker, so surely they'll always be the same by the time you get to that code?


#8

Your function returns a boolean (well, it should). The starting point is to figure out for which data filter the function should return true and for which it should return false.


closed #9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.