Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins are taking time.

So I was thinking to have indexes on columns. Because I read that make indexes on column of join condition. My question is will there be problem if all columns of table are indexed? Or any other way to decrease time complexity of these search filter. Thanks in advance.

More Hint:

  • Table1(main search)-1000 entries primary_key fk1 fk2 fk3
  • Table2-800 entries pk1 ..(8-9 columns)
  • Table3-700 entries pk2 ..(10-12 columns)
  • Table2-850 entries pk3 ..(7-8 columns)

I am using MyISAM engine.

share|improve this question
    
How is it taking a long time when you have 1000 rows of output? –  JNK Jan 20 '12 at 13:45
    
I have given in accordance with ratio. They are ration of millions –  Somnath Muluk Jan 23 '12 at 10:06

2 Answers 2

up vote 1 down vote accepted

When it comes to querying, indexing of a table should never be your first concern.

The queries you plan to use should dictate the indexes you need.

Based on the queries, some columns can be individually indexed. Other queries require compound indexes. The ORDER BY and GROUP BY clauses should provide immediate hints for indexes to make. Not using such hints may result in temp table sorting rather than using the indexes for data in the desired order needed.

Low cardinality of column values should eliminate the need for an index.

Even with these things taken into consideration, you may find that query may need some adjustment (a.k.a. refactoring) for performance gains.

When you reach the point of having the right indexes, not you have to worry about the size of those indexes. For a MyISAM table, this would mean that the .MYI file may grow significantly.

The size of the index file as well as the number of indexes should now be weighed against the performance of your queries, especially if the indexes provide the proper ordering of data and fastest retrieval.

Explain plans for queries may change over time depending on the number of rows, cardinality of columns, number of DELETEs and UPDATEs. Once a query's explain plan changes from what it looked like months ago, you should explore the need to add or remove indexes.

share|improve this answer

Indexing all columns (index on each column) may or may not improve overall performance. That depends index selectivity. Consider creating covering index for your typical query. For instance, if you usually join all 3 tables, the query will mostly benefit on composite index (f1,f2,f3) . Also, if combination of these 3 fields is unique, you can define primary key on f1,f2,f3, so you don't need surrogate PK (assuming there are no other fields on this table)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.