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 ?
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.
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;
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.