Strange problem with MYSQL

[Robbie2003]

The table in question has 5 columns:
An autoincrement mediumint (primary key)
An e-mail address varchar column
A tinyint (boolean) column denothing wether the user has receieved a particular kind of e-mail
Another tinyint (boolean) column denothing wether the user has receieved a particular kind of e-mail
A tinyint (boolean) column denothing wether the user has unsubscribed to the list

There are four indexes:
A unique index on the e-mail column
A unique on each of the boolean columns

Our problem is, any query executed on this table is extremely slow, despite the fact that we're not amending any data in the address column or adding any more. There are currently around 5.5 million rows in the database, which we expect to grow substantially.

The cardinality of the boolean indexes was being reported at None. When the unique index was deleted, the cardinality for all three indexes changed to 2 (which was the expected value). Executing COUNT(*) queries on these indexes are extremely slow, taking up to 4 minutes.

Any queries that change the structure of the table, or the indexes take in insane amount of time, typically 14 to 17 hours. The server is not busy on any other tasks, and is dedicated to the operation of this database.

Inserting a single row takes approximately 31 seconds.

We can see no reason why this table should be so slow in comparison to a similar table we operate, which has a very similar structure and volume of data.

Can you resolve this mystery for me????

 

[Bastien]

Indexes play hell with boolean columns due to cardinality. Better to remove those indexes. As it is, the index isn't used anyway, because there are only 2 values in each of those columns so the whole table is scanned when a query is run and that query is likely to return more than 30% of the rows.

Also each insert requires that each of those indexes on the boolean field gets an insert as well. So any changes that affect the Db need to have the indexes played with as well. That is the bottleneck. Oracle provides something called a Bitmap index which is ideal for these kinds of situations, but MySql does not offer a similar type of index.

Solution is to kill the indexes on the boolean fields as they are not helping the speed of the application.

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[Robbie2003]

Thanks Bastien

I will let you know the result when I have deleted the indexes, these indexes take aprox seven hours each to delete.