Simple query hangs on a specific column condition

I have two servers, and I a dumped the database of the first to restore it on the second.

Everything works great, except that on the second server, I can’t do any selection based on a specific column “user_id” anymore : SELECT * FROM products WHERE user_id = 12 limit 1;

Each time I run it on the second server, it stays still, without any error. And every other request that concerns a selection based on the user_id column is also stuck (where user_id IS NOT NULL, etc.).

I launched mysqlcheck, everything seemed ok, I restarted mysql, but nothing works.

Any idea how to troubleshoot this ?

EDIT :

Curiously, SELECT * FROM products WHERE user_id IS NULL limit 1; works fine.

I might be guessing on this one, but I think the key distribution must be insane.

POSSIBILITY #1

Please run this

SELECT * FROM
(
    SELECT IFNULL(user_id,-1) user_id,COUNT(1) prod_count
    FROM products GROUP BY IFNULL(user_id,-1)
) A ORDER BY prod_count DESC;

If you can send the output to a text file, look for user_id 12 and compare its prod_count to the total number of rows in the products table. My guess is that user_id 12 is probably more than 5% of the table. The Query Optimizer would give up using any index on user_id and just do a full table scan. To make sure that is the case, please run

EXPLAIN SELECT * FROM products WHERE user_id = 12;

POSSIBILITY #2

There may be stale index stats and you need to run

• ANALYZE TABLE products; if there are a lot of INSERTs, UPDATEs.
• OPTIMIZE TABLE products; if there are a lot of INSERTs, UPDATEs, and DELETEs.

GIVE IT A TRY !!!