I'm not certain how MySQL handles the specific case where some columns in a record covered by a multi-column index are updated; it may update the whole index entry, or just part of it, not sure. In any case, yes, there is some overhead associated with having an index on columns that get updated.
In my experience, the overhead is not bad (how's that for quantifying it??) even with multiple indices on a table with 100,000 to 150,000 records. Your table is smaller, I think you said around 30,000 records. If you're concerned about it, you could certainly try an index that covers just Is_id and stype, in either order; it would be an improvement over where you are now. That would avoid index update overhead if those fields never change. It's also frankly something that is pretty easy to experiment with, as dropping and creating indices on a 30K record table should be reasonably quick (around a few seconds?). Also, don't forget the datatype mismatch in the one column, that can have an impact too. Dan On 10/18/06, Mindaugas <[EMAIL PROTECTED]> wrote:
>I agree that individual fields have relatively few possible values - > hopefully, when those are combined in a multi-column index, he will > have a greater number of unique combinations, gaining more out of the > index. That's why I suggested putting stype and Is_id as the first > two fields in the index (though I guess I did not mention that!). > > stype had 6 values, Is_id had 5, so he may have up to about 30 > combinations as the first two fields, which should be enough to help a > lot. ls_id is evenly distributed but stype is not. But thought is interesting. And one question. I mentioned that update:select ratio is 3:1. There are no deletes and inserts. Also update does not touch ls_id and stype fields. Is there index updating overhead then? Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
