-
Index selectivity
I have table with 1 million rows and it has index on one of the columns which is a foreign key. After analyze, distinct_keys value for index on this column is 1000. Index selectivity is not even 1%. If I drop this index, query response time is very bad. I thought index selectivity plays a significant role in response time, but the result from above scenario is quite opposite. Is my analysis is correct? Thanks in advance for any advice.
Thanks
-
Block selectivity. This explains it better than I can:
http://www.hotsos.com/e-library/abstract.php?id=5
-
What is the number of blocks used by the table?
What is the CF value for the index?
Tamil
Last edited by tamilselvan; 02-23-2006 at 10:43 PM.
-
Number of blocks used by table = 72,500
Clustering Factor of index = 1,090,000
Thanks
-
ouch, no oracle running the show at hotsos? link failed with
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.hotsos.com/e-library/abstract.php?id=5' at line 1
I'm stmontgo and I approve of this message
-
Not sure what's going on, but paste the link into your browser, rather than clicking on it.
-
Originally Posted by CHALAVADI
Number of blocks used by table = 72,500
Clustering Factor of index = 1,090,000
Does your predicate have equality or range scan?
Tamil
-
In fact, what's the actual query?
-
Here is my query. It is very simple query.
Select * from daily_transaction
where product_id = 23 ; -- product_id is the foreign key from product definition table.
Thanks
-
What is your explain plan now and is your table analyzed with compute statistics. Generally speaking if product_id column has 1000 distinct values while your table has 1 Million records then the index scan should take lesser time than the non index scan.So since you deleted the index it's going for full table scan now and execution time is more now. Does this explanation Ok or your question is bit different.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|