How I can Optimize this mySQL transaction within java code?

Question

Dear All,

I am new to MySql database. I've large table(ID,...). I select ID frequently with java code and.And that make a heavy load on transaction

 select from tableName where ID=someID

notes:

1.Database could be 100,000 records

2.I can't cache result

3.ID is a primary key 4.I try to optimize time needed to return result from query.

Any ideas for optimization ?

thanks in advance

Answer 1

If you change the query to use a parameter, it might be a bit more efficient. The server would not have to parse and semantic check the statement each time.

select * from tableName where ID = @someID

Then assign the parameter value for each execution. Here is an explanation of using prepared statements.

Answer 2

Have you considere partitioning? Improving Database Performance with Partitioning.

Answer 3

I fail to see the need to optimize. This is a simple query against a very tiny table in database terms and the item inthe where clause is a PK and thus indexed. This should run very fast.