• 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
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
Bartenders:
  • Carey Brown
  • salvin francis
  • Claude Moore

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.
 
Sheriff
Posts: 5931
155
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: 39244
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?
 
Ranch Hand
Posts: 380
2
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.
 
Saloon Keeper
Posts: 20657
122
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.
 
I've never won anything before. Not even a tiny ad:
Create Edit Print & Convert PDF Using Free API with Java
https://coderanch.com/wiki/703735/Create-Convert-PDF-Free-Spire
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!