Possible way to find Username from 1000,000 Users entries

Question

I have Db of 100,000 users in MYSQL. In that DB i am having column ID,username,Fname,Lname, etc.. When www.example.com/Jim or www.example.com/123 (Where JIM is username and 123 is ID in the users table)

I am using MYSQL query : select * from users where ID = 123 OR username = Jim I am executing above query in PHP. Output of the above query is :

| ID | Username | fname  | lname   |
+----+----------+--------+---------+
|123 |   jim    |  Jim   | Jonson  |

My Problem is its taking huge time to select username or ID in the DB.

I have used following query

SELECT * FROMusersUSE INDEX (UsersIndexId) where id=123

Is this right way to call Index ?

EXPLAIN SELECT * FROM  `users` WHERE ID =327

OP

id  select_type  table    type    possible_keys        key      key_len ref rows Extra 
1     SIMPLE       users  Const  PRIMARY,UsersIndexId  PRIMARY     4   const

1

What are your index's ? Post the output from explain select * from users where ID = 123 OR username = Jim — exussum, Dec 18 '12 at 07:22
Indexing, Caching....there are many ways to improve performance. What have you tried? — Dr. Dan, Dec 18 '12 at 07:23
If you dont already have indexes on ID and username I suggest adding them. — PhearOfRayne, Dec 18 '12 at 07:24
Hmmm I tried nothing .. I am trying search Best possible way to select username — Sandeep Kamble, Dec 18 '12 at 07:25
@StevenFarley Can Link me for indexes ? I am using ID as Index (Primary key) — Sandeep Kamble, Dec 18 '12 at 07:26
@SandeepKamble Take a look at this http://stackoverflow.com/questions/3002605/how-to-add-indexes-to-mysql-tables it will help you to add indexes to your tables. — PhearOfRayne, Dec 18 '12 at 07:26
You need to decide if the username or the ID was passed and just search on one, not both. Should be easy since your user ID is numeric. — MrCode, Dec 18 '12 at 07:28

Kjartan · Accepted Answer · 2013-11-12T14:42:46.617

I sugest you take a look at this: How MySQL Uses Indexes

Quoting from the first paragraph:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.

That should help speed up your search.

(Edit: Updated the link to a newer version of the SQL docs)

PS: More specifically, column indexes might be what you want. You can find more info about adding indexes here: Create Index Syntax

I think i am going to add the Indexes . Thanks for the information . — Sandeep Kamble, Dec 18 '12 at 07:36

score 2 · Answer 2 · answered Dec 18 '12 at 07:36

2

To complete @Kjartan answer, you can try the following :

ALTER TABLE users ADD INDEX id_i (`ID`);
ALTER TABLE users ADD INDEX username_i (`Username`);

Your queries should be faster.

answered Dec 18 '12 at 07:36

Alain Tiemblo

36,099
17
121
153

Hmm I think It will take more storage ? – Sandeep Kamble Dec 18 '12 at 07:46
1

Of course it will take more storage! That is the trade off with indexes - more storage, more overhead to add and change data, but potential for much, much faster retrieval. – Lord Peter Dec 18 '12 at 07:51
ID should be PRIMARY KEY or UNIQUE INDEX – Sir Rufo Dec 18 '12 at 08:35
SELECT * FROM `users` USE INDEX (UsersIndexId) where id=123 Is this right way to call it ? – Sandeep Kamble Dec 18 '12 at 09:58
No need to `USE INDEX`, MySQL will do it automatically. Use `EXPLAIN SELECT * FROM users ...` to see if your indexes are uused by MySQL correctly. – Alain Tiemblo Dec 18 '12 at 11:01

Possible way to find Username from 1000,000 Users entries

2 Answers2