C[omp]ute

Welcome to my blog, which was once a mailing list of the same name and is still generated by mail. Please reply via the "comment" links.

Always interested in offers/projects/new ideas. Eclectic experience in fields like: numerical computing; Python web; Java enterprise; functional languages; GPGPU; SQL databases; etc. Based in Santiago, Chile; telecommute worldwide. CV; email.

Personal Projects

Choochoo Training Diary

Last 100 entries

Surprise Paradox; [Books] Good Author List; [Computing] Efficient queries with grouping in Postgres; [Computing] Automatic Wake (Linux); [Computing] AWS CDK Aspects in Go; [Bike] Adidas Gravel Shoes; [Computing, Horror] Biological Chips; [Books] Weird Lit Recs; [Covid] Extended SIR Models; [Art] York-based Printmaker; [Physics] Quantum Transitions are not Instantaneous; [Computing] AI and Drum Machines; [Computing] Probabilities, Stopping Times, Martingales; bpftrace Intro Article; [Computing] Starlab Systems - Linux Laptops; [Computing] Extended Berkeley Packet Filter; [Green] Mainspring Linear Generator; Better Approach; Rummikub Solver; Chilean Poetry; Felicitations - Empowerment Grant; [Bike] Fixing Spyre Brakes (That Need Constant Adjustment); [Computing, Music] Raspberry Pi Media (Audio) Streamer; [Computing] Amazing Hack To Embed DSL In Python; [Bike] Ruta Del Condor (El Alfalfal); [Bike] Estimating Power On Climbs; [Computing] Applying Azure B2C Authentication To Function Apps; [Bike] Gearing On The Back Of An Envelope; [Computing] Okular and Postscript in OpenSuse; There's a fix!; [Computing] Fail2Ban on OpenSuse Leap 15.3 (NFTables); [Cycling, Computing] Power Calculation and Brakes; [Hardware, Computing] Amazing Pockit Computer; Bullying; How I Am - 3 Years Post Accident, 8+ Years With MS; [USA Politics] In America's Uncivil War Republicans Are The Aggressors; [Programming] Selenium and Python; Better Walking Data; [Bike] How Fast Before Walking More Efficient Than Cycling?; [COVID] Coronavirus And Cycling; [Programming] Docker on OpenSuse; Cadence v Speed; [Bike] Gearing For Real Cyclists; [Programming] React plotting - visx; [Programming] React Leaflet; AliExpress Independent Sellers; Applebaum - Twilight of Democracy; [Politics] Back + US Elections; [Programming,Exercise] Simple Timer Script; [News] 2019: The year revolt went global; [Politics] The world's most-surveilled cities; [Bike] Hope Freehub; [Restaurant] Mama Chau's (Chinese, Providencia); [Politics] Brexit Podcast; [Diary] Pneumonia; [Politics] Britain's Reichstag Fire moment; install cairo; [Programming] GCC Sanitizer Flags; [GPU, Programming] Per-Thread Program Counters; My Bike Accident - Looking Back One Year; [Python] Geographic heights are incredibly easy!; [Cooking] Cookie Recipe; Efficient, Simple, Directed Maximisation of Noisy Function; And for argparse; Bash Completion in Python; [Computing] Configuring Github Jekyll Locally; [Maths, Link] The Napkin Project; You can Masquerade in Firewalld; [Bike] Servicing Budget (Spring) Forks; [Crypto] CIA Internet Comms Failure; [Python] Cute Rate Limiting API; [Causality] Judea Pearl Lecture; [Security, Computing] Chinese Hardware Hack Of Supermicro Boards; SQLAlchemy Joined Table Inheritance and Delete Cascade; [Translation] The Club; [Computing] Super Potato Bruh; [Computing] Extending Jupyter; Further HRM Details; [Computing, Bike] Activities in ch2; [Books, Link] Modern Japanese Lit; What ended up there; [Link, Book] Logic Book; Update - Garmin Express / Connect; Garmin Forerunner 35 v 230; [Link, Politics, Internet] Government Trolls; [Link, Politics] Why identity politics benefits the right more than the left; SSH Forwarding; A Specification For Repeating Events; A Fight for the Soul of Science; [Science, Book, Link] Lost In Math; OpenSuse Leap 15 Network Fixes; Update; [Book] Galileo's Middle Finger; [Bike] Chinese Carbon Rims; [Bike] Servicing Shimano XT Front Hub HB-M8010; [Bike] Aliexpress Cycling Tops; [Computing] Change to ssh handling of multiple identities?; [Bike] Endura Hummvee Lite II; [Computing] Marble Based Logic; [Link, Politics] Sanity Check For Nuclear Launch; [Link, Science] Entropy and Life

MySql Efficiency, Security

From: "andrew cooke" <andrew@...>

Date: Mon, 13 Mar 2006 22:32:33 -0400 (CLT)

Rewrote the database for my current project to ensure all queries are
using indices.  It was a fair amount of work - partly because I had
experimented with some ideas that didn't work out (data in statements
rather than tables - even though it was very fixed data, it blocked
optimisation) and some ideas that MySql cannot optimise through (I'm not
100% clear it's MySql's fault, but I think that there are cases where the
view would propogate index information better if it were a merge, but it
can't be because of the definition, which leads to a temporary table).

Anyway, end result is much more clarity and efficiency.

And with the database cleaned up, security can work, which means more
learning - it's not as easy as I thought to find a simple set of
primitives that give you the control you want without a lot of complexity.

Still, progress.  I really think this could be done within the year.

Andrew

Useful SQL Commands

From: "andrew cooke" <andrew@...>

Date: Tue, 14 Mar 2006 07:14:39 -0400 (CLT)

The basic command for tuning selects is "describe select", so to check
that indices are being used you execute, for example:

  describe select * from table where a = b;

Note the a = b condition - this needs to be what will be actuallt used (if
you have no condition, for example, you're going to get an ALL since the
whole table is selected).

This prints a table (make your window wide) that includes the column
"type" - the contents of that column should NOT include "ALL".

I can't work out how to see what the view algorithm is - it seems that
"show create view name" should do this, ut it always gives "algorithm =
default".

An Example Using Group By

From: "andrew cooke" <andrew@...>

Date: Tue, 14 Mar 2006 08:28:23 -0400 (CLT)

Here's an example that requires a full table scan even though, as far sa i
can see, the underlying indices are sufficient:


drop table simple;
drop table duplicates;
drop view grouped;

create table simple (
  idx integer not null auto_increment primary key,
  val integer not null unique
);

insert into simple (val)
values (1), (2), (3), (4), (5), (6);

create table duplicates (
  val integer not null,
  dup integer not null,
  primary key (val, dup)
);

insert into duplicates (val, dup)
values (1,1), (2,2), (3,3), (3,30), (4,4), (4,40);

create view grouped as
  select s.idx, s.val, max(d.dup)
    from simple as s, duplicates as d
    where s.val = d.val
    group by idx;

-- this is slow, because grouping forces use of a temp table

select * from grouped where idx = 1;
describe select * from grouped where idx = 1;

-- this is equivalent, but faster

select s.idx, s.val, max(d.dup)
    from simple as s, duplicates as d
    where s.val = d.val
      and idx = 1
    group by idx;
describe select s.idx, s.val, max(d.dup)
    from simple as s, duplicates as d
    where s.val = d.val
      and idx = 1
    group by idx;

You can see the speed difference from the "describe" output.

Using the view:

+----+-------------+------------+-------+
| id | select_type | table      | type  |
+----+-------------+------------+-------+
|  1 | PRIMARY     | <derived2> | ALL   | <--- BAD!
|  2 | DERIVED     | s          | index |
|  2 | DERIVED     | d          | ref   |
+----+-------------+------------+-------+

Using the direct select:

+----+-------------+-------+-------+
| id | select_type | table | type  |
+----+-------------+-------+-------+
|  1 | SIMPLE      | s     | const |
|  1 | SIMPLE      | d     | ref   |
+----+-------------+-------+-------+

Comment on this post