CODEWITHSUNDEEP
javamysql
ktm5124
ktm5124

Reputation: 12123

MySQL "select distinct" performance

I have the following code in my Java application:

// Method #1
String sql = "select distinct ID from X inner join Y on ... where ..."
List<String> ids = jdbcTemplate.queryForList(query, String.class);

I'm trying to get a list (or set) of distinct IDs. I'm accomplishing this here by using "select distinct".

My question is, would it be faster performance-wise if I omitted "select distinct" and used HashSet implementation instead? (See Method #2.)

// Method #2
Set<String> ids = new HashSet<String>();
String sql = "select ID from X inner join Y on ... where ..."  
ids.addAll(jdbcTemplate.queryForList(query, String.class));

Upvotes: 0

Views: 337

Answers (1)

prashant thakre
prashant thakre

Reputation: 5147

For an example if you have 99K records in your DB but select distinct are only 100. In that situation you will ask for 99K records and will use HashSet to find out the distinct. Oblivious that is time killing as well resource killing.

Its Database side job, let Database to handle it and provide it to you the distinct values so that less amount of data will transfer from one place to another place.

So its better to use select distinct as its faster always compare to other approach.

Upvotes: 1

Related Questions