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]
