[Koha-bugs] [Bug 7785] MySQL-specific syntax in C4::Members columns()

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7785

--- Comment #9 from M. Tompsett <mtompset at hotmail.com> ---
I figured there were multiple ways to solve this. The ideal solution:
1) doesn't change field order
2) doesn't fail on empty table
3) runs comparably well or faster

Method 0: the orignal SHOW COLUMNS way
--- NOT AN OPTION! ---

with borrowers empty and ~100K records.

Method 1: $dbh->column_info
- changes field order
+ works for empty table
- runs slower (~3-3.25ms/iteration -- empty and ~100K, 4000 iterations)

Method 2: select COLUMN_NAME from information_schema.COLUMNS where
TABLE_NAME='borrowers';
+ keeps field order
+ works for empty table
- runs slow (1.25ms/iteration -- empty and ~100K, 4000 iterations)

Method 3: hashref
- changes field order
- fails on empty table
- runs way slower (330ms/iteration for 100K, 100 iterations)

Method 4: hashref limit 1
- changes field order
- fails on empty table
+ fast (0.5ms/iteration for 100K, 40000 iterations)

Method 5: $sth->{NAME}
+ keeps field order
+ works for empty table
- runs way slower (320ms/iteration for 100K, 100 iterations)

Method 6: $sth->{NAME} limit 1
+ keeps field order
+ works for empty table
+ fast (0.4ms/iteration for 100K, 40000 iterations)

This is why my patch uses what I call method 6 when it is the MySQL driver, and
currently 5 otherwise. As new RDBMS backends become functional, I would expect
elsif statements to add the equivalent method 6 SQL query for each one.

-- 
You are receiving this mail because:
You are watching all bug changes.