Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select rows more effective

 
Sun LiWei
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am writting a program to monitor one of my MySQL Database's tables.it will run in a mutil-threaded envionment.So,I have to consider about how to avoid selecting the same row between two threads and how to make the select action more quicklly when there are more than 100,000 rows. Maybe like this:

I don't think this is the best way to do it. Anyone can give me some advices???Thanks anyway.
[ October 14, 2002: Message edited by: Sun LiWei ]
[ October 14, 2002: Message edited by: Sun LiWei ]
[ October 14, 2002: Message edited by: Sun LiWei ]
 
John Carnell
Author
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sun LiWei,
One quick note off of the top of my head. You do not want to do select * from..... You are just going to be bringning back a ton of data that you are not going to use. Try this instead:
select count(*) totalRows from .... WHERE .....
This will count all of the rows and not return all of the row data.
Then do
long totalCount = 0;
if (rs.next()){
totalCount = rs.getLong("totalRows");
}
if (totalCount>100000{
}
One thing that you need to look at to is your index selectivity. If you have only one or two values in the ih_process column you are going to probably end up doing a full-table scan. The more values in this column, the better hits you are going to get off your index.
Thanks,
John
 
Sun LiWei
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your advices Here is another question.I changed the select statement.

but a exception thrown,said the Resultset doesn't updateable.
[ October 14, 2002: Message edited by: Sun LiWei ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic