Can anyone further help to find a solution to this problem! help

[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????

-------------------------------------------------------------------

HANDLE: Bastien
POSTED ON: Nov 20, 2003

REPLY:
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

Robbie2003

Thanks for your help, we have done as suggested and deleted the indexes on the boolean fields but unfortunatly this does not seem to have made any difference.

Can anyone give any further advice, we are really stuck with this one.

 

[Bastien]

Hi again.

Try seeing if there are a lot of connections or that are idle that are limiting access to the server.

There might also be memory issues with the size of the instance etc. This is where an experienced DBA comes in handy. Check the docs for things like waits, concurrent connections, sizing etc.

Also try bouncing the DB, bring it down in a proper shutdown and then shutdown the server and restart both. This might kill some old connections that are hanging around as well as cleaning up the memory on the server

Bastien

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

 

[vanekl]

There may be a lot of old deleted records in the table.
Try exporting the data, drop the table and recreate it,
and importing the data back in. If there were a lot of
deleted records they're gone now.

Also, how much RAM are you using? Your hard drive isn't
thrashing all the time you're performing an op on this table,
is it? Hard drive access is at least a 1000 times slower
than RAM access.

Finally, just a tip, count(primary_key_field) is faster than count(*)
because count(*) has to look at the entire record whereas
count(primary_key_field) only has to retrieve one column.