>> An index on 'gender' may have a cardinality of >> only two or three (male/female(/unknown)) for example.
Never b-tree index such columns ! Oracle (db2 ...rdbms) has bitmap indexes which work fine fork such data. Look at BIN(myset+0) in http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html. Massive load is better without indexes, which are only good for selects. Mathias -----Original Message----- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 19:46 To: Almar van Pel; mysql@lists.mysql.com Cc: 'mathias fatene' Subject: Re: Performance issues when deleting and reading on large table > > It's a probably a case of not having the cardinality of indexes right and > thus making wrong decisions for queries. > - Currently there is not a single query in the application that does not use > the correct index. We only have key-reads. Wich would mean that MySQL is > creating these incorrect indexes? The indexes are not necessarily incorrect, but MySQL also keeps a property called 'cardinality' for each index. It is an estimate of the number of different items in the index. An index on 'gender' may have a cardinality of only two or three (male/female(/unknown)) for example. I've noticed that the cardinality on MyISAM tables can be very wrong and will be updated to a correct value after an OPTIMIZE TABLE. InnoDB seems to estimate the cardinality constantly. > > Deleting a lot of records will have impact on the indexes, so it's quite a > job. The inserts/updates/deletes will also block the table for reading in > case of MyISAM. > - During deletion of records from the table there is no user interaction. > The only person manipulating the table/database is me. That's the reason why > i'm finding this 'strange'. It will still be a massive operation on indexes. If you have many indexes the task will be even harder... > Changing to Innodb would be a great risk I think. Maybe we should think this > over again, but the way the system is configured right now should in my > opion be sufficient enough. It's not a risk, but may take a while to complete (rebuilding the tables). Anyway, you should test it on a seperate database or even a different server. You may also need to redesign the index(es). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
