Querying a MySQL database in Java, is it more efficient to specify which fields you require?

Question

Is it more efficient to query a MySQL database in Java whilst specifying the fields in the query or more efficient to use the * wildcard?

My current code is

try (Statement statement = SQL.connection.createStatement()) {
                    try (ResultSet rs = statement.executeQuery("SELECT player_name, ip FROM " + SQL.database + "." + TableType.PlayerProfile.tableName + " WHERE ip='" + getPlayerIP(args[0]) + "' AND not(player_name='" + args[0] + "');")) {
                        if (!rs.isBeforeFirst()) {
                            sender.sendMessage("§7No accounts are associated with this player's ip");
                        } else {
                            sender.sendMessage("§7" + getServer().getOfflinePlayer(args[0]).getName() + "'s §lpossible §7alternative accounts:");
                            while (rs.next()) if (!args[0].equalsIgnoreCase(rs.getString("player_name"))) sender.sendMessage("§7" + getServer().getOfflinePlayer(rs.getString("player_name")).getName());
                        }
                    }
                } catch (Exception exception) {
                    exception.printStackTrace();
                }

So the query is

"SELECT player_name, ip FROM " + SQL.database + "." + TableType.PlayerProfile.tableName + " WHERE ip='" + getPlayerIP(args[0]) + "' AND not(player_name='" + args[0] + "');"

and then im using the Java code rs.getString("player_name") to get one of fields I need (I do the same for the ip field later)

Is this efficient or should I use the * wildcard instead of specifieng the fields I will be using with the Java code?

Oncaphillis · Accepted Answer · 2014-11-11 01:19:23Z

2

Of course it's more efficient to specify/reduce the fields you are trying to access. Less data to access by the RDBMS. Less Data to transfer via network in case you're trying to access a remote DB. Furthermore The * specifier does not guarantee the order of the returned fields. If your code is supposed to work with different DB Engines you may be surprised to see that the returned column #3 refers to a different content (been there done that). So even if you're trying to access the full width of a statement/table it is still saver to explicitly specify the cols you like to access.

edited Nov 11, 2014 at 1:19

answered Nov 10, 2014 at 23:46

Oncaphillis

1,91813 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Reece Aaron Lecrivain Over a year ago

The database is local and we are going for maximum CPU efficiency. Ignoring any other factors, is the query 'faster' when you specify the fields you need?

Oncaphillis Over a year ago

Of course. Parsing and analyzing the statement is just a blip for the RDBMS but providing the columns you do not need may result in copying around large amount of data if the ResultSet tends to represent large numbers of rows, even on the local system. If however your only talking about a dozen of rows I'm pretty sure there might be other potential bottlenecks. There always are ;-) But remember: Early optimization is the root of all evil.

Reece Aaron Lecrivain Over a year ago

"Early optimization is the root of all evil"? :O

Oncaphillis Over a year ago

O.k. -- the official quote is "Premature optimization is the root of all evil". Many people -- including myself way to often -- have the tendency of squeezing the last 5% percent of performance, before even fully understanding the domain they are coding for. Or if the customer complains about a slow web-shop but only 60% of the basic functionality still is missing or buggy it helps to focus.

Collectives™ on Stack Overflow

Querying a MySQL database in Java, is it more efficient to specify which fields you require?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related