Q
Problem solve Get help with specific problems with your technologies, process and projects.

Table and database design for SELECT to return results at maximal speed

I have table EMP with following fields:

NAME              VARCHAR2(256) 
DEPARTMENT        VARCHAR2(10) 
EMPNUM            VARCHAR2(10) 
SALARY            VARCHAR2(10)

There are 2,000,000 entries in table.

My SELECT is:
SELECT NAME FROM EMP WHERE DEPARTMENT='5' and EMPNUM='456' and SALARY='12345';

My Question is:
How I should design the table and database (and what DB is better to use?) in order for SELECT to return the results with maximal speed?

Thanks a lot,
Jonathan Kukin


The ideal solution would be to use DRI (Declarative Referential Integrity). Assuming that your EMPNUM values are unique (no two employees could ever have the same EMPNUM value)

CREATE TABLE EMP ( 
   NAME        VARCHAR2(256) 
,  DEPARTMENT  VARCHAR2(10) 
,  EMPNUM      VARCHAR2(10) 
   CONSTRAINT XPKEMP 
   PRIMARY KEY (EMPNUM) 
,  SALARY      VARCHAR2(10) 
   )

Using DRI would provide thedatabase engine with the maximum possible information to speed your query.

A workable second best would be to create an index on the column.

CREATEUNIQUE INDEX XPKEMP ON EMP (EMPNUM)

This isn't as good as DRI, but it should be a drastic improvement over nothing at all.

If some of your columns are Foreign Keys (they are the Primary Key or PK in another related table), it will help your overall performance if you declare them in your EMP table too. In your example, the DEPARTMENT column would be a likely candidate for being a Foreign Key.

Any of the major database engines should handle this task nicely. My personal preference for this kind of job is Microsoft SQL Server, but Oracle, DB2, and others would work nicely. As long as there were very few (better yet only one) users, even desktop databases like Jet (used by Access) could handle this problem.

For More Information


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close