How to find repeating records effectively?

Question

I have a registration application for registering 6 courses. The program will check the repeated register users first. Those users that needed to be checked comes from last year. If users registered for one course, they cannot register for any other courses.

---firstroundregisted table structure---

id|englishname  |idcard |
1 |CHAN SIU MING|A1234  |
2 |TAK HO KANG  |Z4123  |

---Contain >5000 last year registered users records for the 6 courses---

The sql in PHP as below.

SELECT * FROM firstroundregisted 
WHERE englishname='".$_POST['studentname']."' 
AND idcard='".$_POST['idcard']."' 
LIMIT 1;

Is this sql the fastest way to find out if the user registered or not?

Others:

I asked a question here : How to increase Max_used_connections? Some gentlemen suggested me to optimizing the sql query so I start a new question to focus on optimizing. Thank you all for your help.

Answer 1

Is this sql the fastest way to find out if the user registered or not?

It's close enough.

If the table is indexed on englishname (and, preferably, idcard as well) then yes. Indeed, if those two uniquely identify each row, then they really ought to be the Primary Key of the table.

If the table is not indexed, then it really ought to be.

However ...

Never use "select *" in Production code.

Never use raw, User-supplied values directly in dynamically generated SQL.

Doing so is one of the quickest ways to get your table and, possibly, your whole database well-and-truly fouled up. The problem is known as an SQL Injection Attack and your code is wide open to one.

Obligatory XKCD Reference: Little Bobby Tables.

Your best plan would be to move to using Prepared/Parameterised Statements but, failing that, you must "escape" the values from the User so that they form valid and safe SQL.