• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

How to use index in database search

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, im quite new with the SQL and Java. I need to know how to implement index syntax for searching in database that have millions of record and i want to optimize the search process for this.

Can the index syntax use replacing the select syntax here?



Please assist.
 
Sheriff
Posts: 6108
157
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, syahida zainal, and welcome to the Ranch!

Indexing a table is something that is done on the server side as an administrator.  Are you able to administer this database?  What database type is this?  I don't know of any way to index a table as a client.  

Concerning your post: if you are posting code, be sure to UseCodeTags (that's a link).
 
syahida zainal
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Knute, i didnt know that I have to use Code Tags.

Yes, Im able to administer the database. Im using MySQL Server for the database. Can you please explain more about indexing table on the server side as an administer. Thank you.
 
author & internet detective
Posts: 39392
763
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a complicated query. The right way to improve the performance is to look at your database. Can you run an "explain" query on it. This will tell you what table is being scanned in full and what indexes are or aren't being used.

Also, are you really using all those columns from 50K rows? For what?
 
Rancher
Posts: 457
6
IntelliJ IDE Spring Fedora
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You add an index to a table like any other statement.  If you index certain columns it will update the index everytime you make changes to the table.  Indexing makes finding a value faster because the db knows where to start looking instead of looking through all of the rows.
 
Bartender
Posts: 20924
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indexes don't even exist in the theoretical SQL defined by Codd and Date. But try to get along without them in real life!

An index, conceptually, is simply a file containing pairs of keys and record (row) pointers. Since the index "file" is smaller than the actual target table and is organized for fast searching (often using a b-tree or hash), you can find the actual record much more quickly than if you simply had to grind through the target table row by row looking for a match.

You can define indexes as either unique or non-unique. for example, you could have employee records containing their department ID and make an index on department ID, and that would presumably be a non-unique key. On the other hand, the employee ID would almost certainly be unique. You can also create indexes based on computed values or concatenations of multiple field values, but that's getting a bit complex for this discussion.

A Primary Key is an index with unique values. Internally, the DBMS may even optimize table organization based on the knowledge of the primary key definition, but the important thing to the outside world is that since it's a unique-values index, any attempt to add a new record with the same key value as an existing record will fail.

Indexes are defined via DDL, not SQL, and thus no special coding to the SQL is needed to use them. The only difference between a "WHERE" clause component referencing an index and one referencing a non-index value is performance.
 
syahida zainal
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you eveeryone that help in explaining about index. I now understand and get the idea how to use it.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!