MySQL process uses more than 100% CPU usage and need about 1 GB of memory

Question

I am running MySQL server on the server's which has following specifications -

Dual Xeon Quad Core 2.0, 2048 MB RAM, 1x 160 GB SATA Fedora Core + SSH

But MySQL process for inserting 10000 records take more than 100% of CPU and up to 1 GB of RAM. It's a plain insert statement.

Why is MySQL is taking so much of memory and what can done about it?.

Answer 1

The heaviest part in your query is the SELECT:

Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer
Where UserID Between 1 and 5000
and CustomerID in (Select CustId from Cust_Details Where CustName like 'Mi%')

The nested query probably is repeated for each row. You can check this running EXPLAIN PLAN + all the SELECT query. I guess the 'like' operator is used against a non-indexed column. In this case (like 'xyz%') a simple index can improve performance a lot.

[Added: moreover, SELECT CustId ... must output id's that are greater than 5000, that aren't needed at all. A composite index (CustId, CustName) on Cust_Details must also be useful.]

Try usign a join instead:

Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer c, Cust_Details cd
Where c.UserID Between 1 and 5000
and c.CustomerID=cd.CustId
and left(cd.CustName) = 'Mi'