This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!
  • 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
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

How to use index in database search  RSS feed

 
Greenhorn
Posts: 3
  • 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.
 
Marshal
Posts: 5995
156
Chrome Eclipse IDE Java Postgres Database Ubuntu VI Editor
  • 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: 3
  • 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: 39285
727
Eclipse IDE Java VI Editor
  • 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: 427
6
Fedora IntelliJ IDE Spring
  • 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: 20766
124
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • 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: 3
  • 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.
 
Get me the mayor's office! I need to tell him about this tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!