MySQL Forums
Forum List  »  Performance

Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Posted by: Aftab Khan
Date: March 20, 2009 11:10AM

#Remove TIM_p_use index

My Bad, try using convering index.

ALTER TABLE genericitems drop index TIM_p_use, add index (TypePkString,p_user);

you should see similar to following Explain output ( I have used index hint here to force covering index)

mysql> EXPLAIN ( SELECT item_t0.PK, item_t0.p_date FROM genericitems item_t0 use index (TypePkString) WHERE ( item_t0.p_user = 1144444444448336 ) AND (item_t0.TypePkString = 23444444444499984 ) ) UNION (SELECT item_t0.PK, item_t0.p_date FROM genericitems item_t0 use index (TypePkString) WHERE (item_t0.p_user IS NULL) AND (item_t0.TypePkString = 23444444444499984 ) ) ORDER BY p_date DESC;
+----+--------------+------------+------+---------------+--------------+---------+-------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+--------------+---------+-------------+------+----------------+
| 1 | PRIMARY | item_t0 | ref | TypePkString | TypePkString | 17 | const,const | 1 | Using where |
| 2 | UNION | item_t0 | ref | TypePkString | TypePkString | 17 | const,const | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------+------+---------------+--------------+---------+-------------+------+----------------+
3 rows in set (0.00 sec)

And then kindly test this sql and see how long does it take?



Edited 2 time(s). Last edit at 03/20/2009 11:13AM by Aftab Khan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
2374
March 20, 2009 11:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.