How to properly test query performance

Question

I have a function that takes either an array of IDs or a singular ID as an argument. If an array is passed, it is imploded on comma to make the IDs query friendly.

There is a query inside this function that updates the records for the IDs that were passed.

The query is as follows:

"UPDATE tbl_name SET enabled = 1 WHERE ID IN (" . $IDs . ")";

Now, I'm wondering if there's a performance issue with using tha above query for the array and something like:

"UPDATE tbl_name SET enabled = 1 WHERE ID = " . $IDs;

if only a single ID is passed.

I tested both queries in the MySQL query browser for the same ID and got a performance of .02 seconds for both queries - a trivial difference in performance.

Is simply testing the two in the query browser an ok way to check the optimal query performance?

It's just that I don't want to pollute my code with two queries in the one function if it is not necessary.

Answer 1

you can try running both queries in big loops with random IDs

Answer 2

this is what i would use.. and actualy this is what i usualy use when i need to update my mysql.

"UPDATE tbl_name SET enabled = 1 WHERE ID = " . $IDs;

but if you realy want to compare this 2.. i suggest you give them a lot of ids o proccess and then you will know which one is the best.. for a single id the preformance is the same.. but i dont think the performance for 100 id`s will be the same. let me know the result please.thanks

Answer 3

I would lose the second query and only leave the first one to make the code more readable and maintainable.

if (is_array($id)) $id = implode(',', $id);
$query = 'UPDATE table SET marked = 1 WHERE id IN ('.$id.')';

That way you'll get either one of these sample queries:

UPDATE table SET marked = 1 WHERE id IN (15);
UPDATE table SET marked = 1 WHERE id IN (21,12,15,16);

Here's what MySQL manual says about the usage of operator IN:

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants.

I'd also suggest to "don't fix what isn't broken" and better take care to write a clean, readable and maintainable code, because your time is more valuable than your server's time especially if it's a millisecond or less. First look at the performance of an application and if it's okay then you're done - move on to other things you can do with your time. If it's slow - measure it (simple time measurements will do) to pinpoint what is slow and then only optimize that. Usually what is slow is some big messy select from lots of tables with lots of joins, but not a simple update like this. Also keep in mind that updates (usually) are rare and the user can wait for them, so focus on optimizing selects.

Answer 4

If you are looking to be thrifty on cpu cycles and want performance go with IN . Think of it this way .. you have to take 10 items to ur neighbors house ... which will be faster? taking all 10 at the same time or doing it one by one.. even if its a millisecond , when multiplied with the number of instances , it would become significant.

Just imagine what would happen in the background when u send one query to mysql , the mysql engine needs to compile every query and then process it and dont forget the part where PHP has to communicate over the network to send ur query to mysql.

So if u are sending 10 queries instead of one .. u multiply the processing time. This is exactly why "Views" are faster than standard select statements.