CODEWITHSUNDEEP
mysqlperformanceindexing
Haradzieniec
Haradzieniec

Reputation: 9340

MySQL index performance... Should I create an index on this simple table?

I've read about 20 topics about index performance, but still have several questions...

  1. Would you recommend to index userid column in a table that contains only about 2000 rows, the columns are id (autoincrement), userid (unique, like ssn), first_name, last_name, phone_num and it displays first_name, last_name, phone_num on every page when user logged in, by selecting that data from the table using WHERE userid='123-45-67890'?

  2. The same question as 1), but the number of rows increases with time and could be much more than 2000?

Upvotes: 0

Views: 109

Answers (1)

stivlo
stivlo

Reputation: 85476

it displays first_name, last_name, phone_num on every page when user logged in, by selecting that data from the table using WHERE userid='123-45-67890'

Absolutely, go for it, instead of scanning all the rows a few seeks will be enough. You'd be using the query on every page. Have you thought about storing that data in the session, though?

You have to keep your indexes on a minimum when you think that a table will have many writes (for instance you're logging something). In a user table, you'll most likely have lots of reads and few writes.

Also, you can consider not to add an index if the query benefiting from it, is run infrequently and its performance is acceptable.

Without an index:

EXPLAIN SELECT id_person FROM sl_person WHERE username='admin'

Using an index:

EXPLAIN SELECT id_person FROM sl_person WHERE username='admin'

Upvotes: 3

Related Questions