MySQL indexes behaving badly

If you have a MySQL query that for no apparent reason stops working or no longer behaves as it used to, the first thing to do is EXPLAIN it. If the explain is unreasonable (for example, using different indexes then it used to or using an index but still scanning millions or rows) then you should try to CHECK TABLE.

One thing that I accidently found out was that CHECK TABLE also updates the key statistics. This made many problems magically disappear.

A few things that should be noted:

1. CHECK TABLE will only update key statistics for MyISAM tables
, for InnoDB you must use OPTIMIZE TABLE.

2. If you use OPTIMIZE TABLE to update the key statistics you should be aware that OPTIMIZE TABLE does other (good) things as well, so it usually takes much longer and it locks your table while doing it. Also, for InnoDB it actually re-builds the table using ALTER TABLE.
   
3. The CHECK TABLE have several options. Only the MEDIUM (the default) or EXTENDED update the key statistics. (i think).

So, if your indexes stop working, or just mis-behave, try to check them.

MYSQL Query Optimization: Avoiding ORs

So, you've probably heard that using ORs in your queries is heavy and should be avoided when possible. Here is a live example. We had the following query running on a medium sized table in MYSQL (around 200K rows):

SELECT count(*)  FROM interactions p
   WHERE (
       (p.employer = 69 AND p.flag1 = 1)
       OR
       (p.employee = 69 AND p.flag2 = 0)
       )

Let's think of interactions as a table holding the interaction between employers and employees, and this query should tell the user the total number of interactions where user number 69 is involved.

The query looked pretty innoccent on an idle MYSQL server taking around 200ms, but when the server got busy the query execution time was reaching 5-6 seconds.

Altough we did have indexes on interactions.employer and interactions. employee doing EXPLAIN showed that MYSQL was not using them.

After some digging and fiddling and trying we ended up with:

SELECT count(*) as count FROM (
   SELECT 1  FROM interactions p
       WHERE (p.employer = 69 AND p.flag1 = 1)
   UNION ALL
   SELECT 1  FROM privatemsg p
       WHERE (p.employee = 69 AND p.flag2 = 0)
) t2

This query took on the busy server less then 250 milliseconds and less then 80ms on the idle server. Doing EXPLAIN showed that MYSQL was doing two queries, and using the correct index on each one.

After some more digging we noticed that if we EXPLAIN the original query on the idle server the optimizer occasionally converts it to a UNION, but for some reason this did not always happen and in any case took about twice the time then the UNION query.

To sum up the results here is a simple table

Query type	Server activity	Query Time
Using OR	Idle	200ms
Using OR	Busy	5500ms
Using UNION	Idle	78ms
Using UNION	Busy	189ms

Conclusion: always consider alternatives to OR, but make sure you check them well against real-time examples.

A Further Note: When timing queries in MYSQL alway use the optimizer hint /*! SQL_NO_CACHE */ to make sure you are not getting results from the query cache (if you have one set up).

Remote Root Access to MySql

To help me remember:

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST" = PASSWORD('new_password');
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit;

Source: ben robison :: Howto: Remote Root Access to MySql

MySQL 5.1 Clustering Might Just Actually Work

As I previously wrote mysql 5.0 clustering was a big issue for us since all data as placed in memory. It seems that mysql 5.1 clustering is going to be much better, including the ability to store non-index columns on disk (indexes and indexed columns are still stored in-memory), do (limited) cluster-to-cluster replication, and better varchar support for memory columns. Pitty it's only release candidate at the moment. can't wait for the GA to come out.

mysqltop.sh

If you need to monitor mysql proccesslist, dump the following to a shell script and chmod 755. (ofcourse you will need to add passwords etc. if you have any;)

#!/bin/sh

watch 'mysql<<EOF
show processlist;
EOF
'

(Control+C will let you out)

MySQL Cluster

I did some research on MySQL cluster so i can recommend one of our customers whether to use it or not. It turns out that the MySQL cluster is very different from other DBMS clustering solutions in that it distributes the data across the nodes (synchronously) and then stores it on the disk (asynchronously). This is all very nice, but at the moment all data is stored in memory, so if I have a 120GB database I would need around 150GB memory on each node, which is silly.

The Wikipedia Page on MySQL Cluster gives more details.

Waiting for MySQL to get better...

UPDATE: It seems that this is no longer the case for the 5.1 version of mysql. See here.