[Proposal] Suppress superflous table names (speed and readability increase)

rubyonrails-core
1

Active Record prefixes all column names with the table name when generating a query. This is necessary to disambiguate field names if a query uses a join. However, most queries only ever hit a single table and thus in most cases the table name is not needed. It would make the development log much easier to read if we could suppress table names when they’re not needed.

Current behaviour

A typical rails query wil look like this

  SELECT `accounts`.* FROM `accounts` WHERE `accounts`.`id` = 123 LIMIT 1

And if you use a descriptive table name and pick a few fields it can easily get very noisy:

 SELECT `payment_settings`.`id`, `payment_settings`.`account_id`, `payment_settings`.`provider` FROM `payment_settings` WHERE `payment_settings`.`id` = 123 LIMIT 1

Desired behavior

A log like this would be much easier to read

  SELECT * FROM `accounts` WHERE `id` = 123 LIMIT 1
  SELECT `id`,`account_id`,`provider` FROM `payment_settings` WHERE `id` = 123 LIMIT 1

There is even a performance benefit from reducing the number of bytes sent to the log and to the database. I’m not familiar enough with the code base to understand the implications for the implementation, but to the uninitiated it would seem that Active Record should not have too much trouble deciding whether more than one table is involved when generating the query?

2

I did a quick benchmark in the rails console, simply running that second query shown above 1000 times, once with and once without the table name prefix, and got I a >25% speed improvement… That seems suspiciously high but I tried several times and can’t find a problem, would be great if someone can confirm that. This is on an M1 Mac with MySQL 5.7.

ActiveRecord::Base.logger=nil
n=1000
Benchmark.bm do |x|
  x.report {n.times {ActiveRecord::Base.connection.select_rows('SELECT `id`,`account_id`,`provider` FROM `payment_settings` WHERE `id` = 2 LIMIT 1')}}
  x.report {n.times {ActiveRecord::Base.connection.select_rows('SELECT `payment_settings`.`id`, `payment_settings`.`account_id`, `payment_settings`.`provider` FROM `payment_settings` WHERE `payment_settings`.`id` = 2 LIMIT 1')}}
end

       user     system      total        real
   0.024767   0.005599   0.030366 (  0.059816)                                      
   0.017294   0.003379   0.020673 (  0.036394)
3

We could also remove the superfluous backticks “`” in most cases

1 Like