CODEWITHSUNDEEP
sqlperformance
Ilya Gazman
Ilya Gazman

Reputation: 32226

SQL design for performance

I am new to SQL and I have a basic question about performance.

I want to create a users database which will store information about my users:

Later I want to perform a SELECT query on: Id, Log in name and Real name.

What would be the best design for this database, what tables and what keys should I create?

Upvotes: 0

Views: 92

Answers (2)

Richard Hanley
Richard Hanley

Reputation: 328

Just to add to the @homes answer you should work out what sort of queries you will be running and then optimize for those sorts of queries. For example if you are doing a lot of writes and not as many reads having lots of indexes can cause performance issues. It's a bit like tuning an engine for a car, are you going to be going quickly down a drag strip or are you tuning it for driving long distances.

Anyway you also asked about the NAME column. If you are going to be matching on a varchar column it might be worth investigating the use of FULLTEXT Indexes.

http://msdn.microsoft.com/en-us/library/ms187317.aspx

This allows you to do optimized searchs on names where you might be matching parts of a name and the like. As the @homes answer said it really does depend on what your queries and intent is when writing the query.

Might be worth making the table and using the query execution plan in something like SQL management studio against your queries and see what impact your indexes have on the amount of rows and sort of looks up that are happening.

http://www.sql-server-performance.com/2006/query-execution-plan-analysis/

Upvotes: 0

home
home

Reputation: 12538

If it's only about those 4 fields it looks like just one table. Primary key on ID, unique index on LoginName. You may not want to store the password, but only a hash.

Depending on your queries, create different indexes. Furthermore, you may not need the ID field at all.

UPDATE: Creating an index on certain column(s) enables the database to optimize its SQL statements. Given your user table:

USER
    USER_ID BIGINT NOT NULL
    LOGIN_ID VARCHAR(<size>) NOT NULL
    PASSWORD VARCHAR(<size>) NOT NULL
    NAME VARCHAR(<size>) NOT NULL

CONSTRAINT PK_USER PRIMARY KEY ( USER_ID )

The databases I know will automatically create an index on the primary key, which in fact means that the database maintains an optimized lookup table, see WikiPedia for further details.

Now say, you want to query users by LOGIN_ID which is a fairly common use case, I guess, you can create another index like:

CREATE INDEX I_USER_1 ON USER ( LOGIN_ID asc )

The above index will optimize the select * from USER where LOGIN_ID='foo'. Furthermore, you can create a unique index instead, assuming that you do not want duplicate LOGIN_IDs:

CREATE UNIQUE INDEX UI_USER_1 ON USER ( LOGIN_ID asc )

That's the whole story, so if you want to optimize a query for the users real name (NAME), you just create another index:

CREATE INDEX I_USER_2 ON USER ( NAME asc )

Upvotes: 2

Related Questions