MySQL: Selecting a username?

WinSrev · 11-25-2007, 09:42 PM

Hey,

I've been trying to figure out just what would be the quickest way to selecting a username from a database, there's no chance of selecting through fulltext as some usernames are less than 3/4 characters and to me the standard select could be slow with a lot of members. It has to be selected as a username to get the ID.

Anyone have any other suggestions?
Thanks!

Salathe · 11-25-2007, 09:50 PM

I don't understand fully, why wouldn't a standard SELECT be of use? If you index the column, it shouldn't be slow.

SQL Code:

SELECT id, username, blah 
FROM users
WHERE username = 'salathe';

WinSrev · 11-25-2007, 10:27 PM

Ah, but, is there any better way of doing it or is that the best way possible?

Wildhoney · 11-26-2007, 12:11 AM

How can you have a better way? Set the user name field as an index and specify that it's unique. Can't get much faster than that.

WinSrev · 11-26-2007, 12:15 AM

Ah, okay, thanks :)

DragonBe · 11-26-2007, 09:04 AM

Hi there,

If you know your way around MySQL, you can create an index on a part of a column so you can actually index the first three or four characters (do mind yourself you cannot make this an unique index)

The statement shown here creates an index using the first 4 characters of the name column:

CREATE INDEX part_of_name ON customer (name(4));

Ref: http://dev.mysql.com/doc/refman/5.1/...ate-index.html

Cheers,

DragonBe

Tanax · 11-26-2007, 12:20 PM

Additionally, you may want to read Wildhoney's article about Sprintf:
Securing your MySQL Queries with Sprintf

More secured that way

WinSrev · 11-26-2007, 08:57 PM

Firstly, the table was already indexed, just thought there might be a better way and sprintf doesn't instantly make it secure, my method is secure as it is and does not use sprintf.

Wildhoney · 11-27-2007, 01:06 AM

Use EXPLAIN before your code. Like so:

sql Code:

EXPLAIN SELECT id, username, blah 
FROM users
WHERE username = 'salathe';

You want to avoid ALL queries. But on an index it will be super fast anyway.

CoryMathews · 11-27-2007, 10:45 PM

you could use a stored precedure as well. this away the query is pre-compiled saving you that time.

Wildhoney · 11-28-2007, 04:18 AM

I tried stored procedures a while ago. Funny thing is, I know how to code using stored procedures, I just can't seem to get them to behave well with PHP. When I last tried, roughly a year ago, there were so many bugs in PHP relating to stored procedures - connections kept getting dropped and other miscellaneous issues, that I just gave up in the end. And that was with mysqli, too!

CoryMathews · 11-28-2007, 07:11 PM

i haven't done it in php yet, its on my todo list but i have done them in coldfusion and they were pretty easy. but most things seem to be harder in php.