-3

There is a user table which has 10 million records, following is the schema of the table user (user_id, user_name, first_name, last_name, address)

when following select is run on the above table it takes 18 minutes to run the query and the entire database slows down during this time.

select * from user where user_id=1000 and username='test';

how do we speed up the select and/or improve database performance in this case?

user2116018
  • 13
  • 4
  • 3
    What RDBMS? Have you checked the execution plan? Are there any indexes on the table? – Dan Jan 23 '14 at 19:54
  • First thing to look at is typically indexes, but you haven't mentioned what platform you are using to allow us to help you any further. – Mike Gardner Jan 23 '14 at 19:55
  • 1
    For starters only select the columns you need... `*` will be expensive if you are fetching large quantities of data that are unnecessary. – scunliffe Jan 23 '14 at 20:01
  • Its general question for optimizing performance under such scenario on RDBMS. It was asked in technical aptitude. well thank you – user2116018 Jan 25 '14 at 14:09

2 Answers2

1

here are some tips for you,

1.Add index on userid and username (grouped index) if userid is not unique/primary key that will improve performance for sure

2.if possible make userid autogenerated and primary key,if you do that your query will reduce to

"select * from user where user_id=1000" which will faster with single index (userid which is default as its a primary key) and that will save space compared to grouped index

3.basic rule for perfomance improvement, always fetch required columns only (select * from is worst always)

do not forget to run runstats and reorg after index creation

clustered index mentioned above will also help but more space is required for clustered index.(why waste more space if your query could be improved or reduced in your case)

hope this helps..

Nachiket Kate
  • 8,473
  • 2
  • 27
  • 45
0

Make sure you have a clustered index on user_id, and a non_clustered index on 'test'.

Guaranteed performance boost of 10,000%

datagod
  • 1,031
  • 1
  • 13
  • 21