index

[dummyagain]

I would like to know how to use the index to speed up the query?

For example, if i use "select primary_key, subject1, subject2 from table1 where subject1=1 and primary_key=2", then should i index the primary key or what?

Thank you

[olivthill]

A primary key has its own index, therefore it is useless to add an index on the primary key.

Indexes might increase the speed for fields mentioned in the "WHERE" part of the query. In your example, "where subject1=1 and primary_key=2", since primary_key is already indexed, the only two other indexes you could add would be on "subject1", and on "primary_key, subject1" (several databases allow the definition of an index on a combination of several fields).

But I suppose your query is much more complex. If you want more advice, you could post here your actual query, with the approximate volumes of the tables and, of course, the name of your database (Oracle, MySQL, Access, SQL server, ...).

[dummyagain]

something like

company (company_ID, company_Name, address, profit)

partnership (company_ID, partner_ID, date)

select company.company_ID, company_Name, partner_ID, profit from company, partnership where company.company_ID=partnership.company_ID and profit > 10000 order by company_Name;

Then should i index the company.company_ID, partnership.company_ID and profit, company_Name?

Actually I don't really understand why using index can speed up the query, can you explain me why? Thank you so much....

[olivthill]

An index on partnership.company_ID might be useful.

Without an index, the database engine is browsing the entire table, e.g. partneship, searching for the value of a field, e.g. partneship.company_ID, matching the value of another field, e.g. company.company_ID. If the patnership table holds 1000 records, then 1000 records are read.

With an index, the database engine has a sorted list. The search is quicker. It is more or less equivalent to a binary search, therefore needing to read about 10 records among a set of 1000 records (because 2 ** 10 = 1024). Then, the database engine, will quickly find the following records having again patrneship.companyID = company.company_ID, because they are coming next in the index. Then, the database engine will stop just after it has found a different value, without having to go further.

[dummyagain]

company (company_ID, company_Name, address, profit)

partnership (company_ID, partner_ID, date)

select company.company_ID, company_Name, partner_ID, profit from company, partnership where company.company_ID=partnership.company_ID and profit > 10000 order by company_Name;

in my case, the company_id and partner_ID in the partnership are key, so will it be any difference? And if we only need to index one of the subjects or can we index more than one subject?