MySQL fastest way to query rows

Question

My problem is that i don't yet have a good server to test this on but i'd like to know if it faster to use:

$sqlgetg = "SELECT assignments.id FROM assignments LEFT JOIN userassignments ON assignments.id = userassignments.assignmentid WHERE userassignments.userid = '" . $row['id'] . "' AND userassignments.assignmentid = '" . $assignmentid . "'";

or

$sqlgetg = "SELECT NULL FROM assignments LEFT JOIN userassignments ON assignments.id = userassignments.assignmentid WHERE userassignments.userid = '" . $row['id'] . "' AND userassignments.assignmentid = '" . $assignmentid . "'";

Since i have to check if there even is an assigment for the user with assignment id of x? I don't need anything else but this: if(mysqli_num_rows($resultgetg) > 0)? In both cases phpMyAdmin gave me the row number that i wanted. (I checked it with without WHERE and it still worked.)

EDIT: I don't know how and why NULL works but it does...

Either way, you should be using prepared statements. And indexes. — Strawberry
– Strawberry, Commented Oct 30, 2017 at 8:15
Unless NULL is a column name, the second statement shouldn't work... — Leah
– Leah, Commented Oct 30, 2017 at 8:15
It's just showing NULL the same number of times as the result records... — Leah
– Leah, Commented Oct 30, 2017 at 9:21
@Leah that is not the point... if(mysqli_num_rows($resultgetg) > 0) takes NULL as an input. I just needed to know which one is faster? — user7838288
– user7838288, Commented Oct 30, 2017 at 11:34

Michael - sqlbot · Accepted Answer · 2017-10-31 00:57:21Z

0

You can select any static value "from" a table if you just want to count the rows returned... SELECT NULL or SELECT 42 or whatever. But both of your strategies are in fact suboptimal, because they require unnecessary work by the server and unnecessary network traffic and unnecessary handling of the result set that you are only planning to discard.

If counting rows is what you want, then actually do that. you should SELECT COUNT(*) AS how_many_rows ... and let the server count the rows and return a single row with a single column ("how_many_rows" -- make up your own alias) containing the count.

answered Oct 31, 2017 at 0:57

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7838288 Over a year ago

So using COUNT would just recognize the rows instead of trying to find data from them and this would be optimal for the server?

Michael - sqlbot Over a year ago

It should be optimal for server, client, and network. Also, there is no need for the left join... no need for an inner join, either... You only need the count from the userassignments table. If you have a unique index on (userid, assignmentid) in that that table, the optimizer should go right to it, count the rows, return the values. Data must be read in order to be counted but the index would be a covering index, thus minimizing the actual number of bytes actually read from disk.

Collectives™ on Stack Overflow

MySQL fastest way to query rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related