Re: High cpu usage on server by mysql process

[Priya Pawar]

Hi,

SHOW VARIABLES LIKE 'query%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)

explain select a.asset_id,t.reference_number, t.owner_reference, t.product_type, t.model_number, t.series, t.creation_date, if(a.completed='Y',true,false) as complated, if(a.accepted='Y',true,false) as accepted, a.updated_on, a.version,a.created_by, a.updated_by,a.locked_by,a.locked_on,a.locked_status,a.effective_end_date,a.hierarchy_id,t.exceeded as taxonomyExceedsFiftyChars,t. reason, 'ods' as source, a.created_on,a.ul_asset_id,'' as collection_id,t.taxonomy_id from taxonomy t inner join asset a on a.taxonomy_id = t.taxonomy_id where a.completed='Y' and a.effective_end_date>now();
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+--------+-------------+
| 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 472690 | NULL |
| 1 | SIMPLE | a | ref | asset_fk2 | asset_fk2 | 153 | ip_odsdb_qa.t.taxonomy_id | 1 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+--------+-------------+