TalkPHP - MySQL: Selecting a username?

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - MySQL: Selecting a username? (http://www.talkphp.com/mysql-databases/1517-mysql-selecting-username.html)

WinSrev

11-25-2007 09:42 PM

MySQL: Selecting a username?

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:
http://www.talkphp.com/showthread.php?t=1062

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.

All times are GMT. The time now is 03:14 AM.