How would InnoDB handle this query?

DarkLight's picture

He has: 287 posts

Joined: Oct 2007

Hi guys.
Not a high priority question, as the following is used only occasionally and the function it is a part of is an Admin Lookup tool, so spamming the DB with it is out of the question.

I was wondering if, when your specify multiple conditions in WHERE and give it a LIMIT 1, would the query continue to go through each and every WHERE condition, or would it stop and return the result the first time it finds a match?

For example:

<?php
$query
= mysqli_query($GLOBALS['dblink'], "SELECT * FROM users LEFT JOIN online ON OnlineUID = UserID WHERE UserID = '$info' OR UserIP = '$info' OR OnlineIP = '$info' OR UserEmail = '$info' OR UserName LIKE '%$info%' LIMIT 1");
?>

This, as you can see, is a TERRIBLE query, and normally wouldn't come anywhere near my site. But in this instance, it's actually very effective at doing its job.

BUT, if say, it matched the UserID and was able to return a result, would it then continue to look through UserIP, UserEmail and UserName, just because it's in the query? or would it stop searching once it matched UserID?

Because I've tried to set up the query in such a way that if the latter is true, it would be relatively efficient as the WHERE fields are positioned from Most to Least ideal in terms of Indexes. The last one (UserName) being by far the worst.

Hope that all makes sense, and hopefully someone can enlighten me Smiling

Thanks in advance.

Powerful laser pointers, tactical flashlights, plasma lighters, sensory lights and more. Free worldwide shipping, PayPal secure checkout, no signup required.

https://coldcast.net