As for question 1: the overhead will be exactly the same in both index layout scenarios. The only deciding factor is how your application uses this data.. the choice of structure has only to do with finding what accelerates application quiries most effectively.
Question 2: by all means, add the second index. The first index would be of no value to you whatsoever when field2 is your criteria. Not to trivialize the real cost the can and do come with indexes, but the 'real' cost factor is in the addtional resources in both time and hardware when an application is forced to labor away aginst insufficiently optimized data structures. - michael On 5/16/07, Les Fletcher <[EMAIL PROTECTED]> wrote:
I have two questions about the overhead associated with updating the indexes when records are added, deleted or modified. Lets say I have a table of 1 million records that is partitioned on partitionedfield (10 distinct values). id int partitionedfield int property1 int property2 int property3 int property4 int property5 int id is unique over the whole table but property1 has 10 distinct values, property2 has 100 distinct value, property3 has 1,000 distinct values, property4 has 10,000 distinct values and property5 has 100,000 distinct values Would the overhead associated with maintaining the indexes: index(partitionedfield,property1) index(partitionedfield,property2) index(partitionedfield,property3) index(partitionedfield,property4) index(partitionedfield,property5) be different than the overhead associated with maintaining: index(property1,partitionedfield) index(property2,partitionedfield) index(property3,partitionedfield) index(property4,partitionedfield) index(property5,partitionedfield) How does the combination of the order and cardinality of a field affect the maintaining of an index? I realize the answer might be of the "it depends" type, but I am just trying t get a better feel of the costs of having indexes around. My second question has to do with abusing multifield indexes. Let's say I have an index index(field1,field2). Most of my queries use this index appropriately, restricting on field1 and field2 or restricting on field1 grouping or sorting on field2. I also have another query that only uses field2. Would it be better to put an index on field2 (index(field2)) or just throw a catch all clause in the query for field1 (i.e., field1 > 0 if it was a postive int field) and force it to use the previous index. Long email. Thanks for reading it and getting to this point. :o) Les -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
