| Andrew Cooke | Contents | Latest | RSS | Twitter | Previous | Next


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

Lepl parser for Python.

Colorless Green.

Photography around Santiago.

SVG experiment.

Professional Portfolio

Calibration of seismometers.

Data access via web services.

Cache rewrite.

Extending OpenSSH.

C-ORM: docs, API.

Last 100 entries

China Gamifies Real Life; Can't Help Thinking It's Thoughtcrime; Mefi Quotes; Spray Painting Bike Frame; Weeks 10 + 11; Change: No Longer Possible To Merge Metadata; Books on Old Age; Health Tree Maps; MRA - Men's Rights Activists; Writing Good C++14; Risk Assessment - Fukushima; The Future of Advertising and Surveillance; Travelling With Betaferon; I think I know what I dislike so much about Metafilter; Weeks 8 + 9; More; Pastamore - Bad Italian in Vitacura; History Books; Iraq + The (UK) Governing Elite; Answering Some Hard Questions; Pinochet: The Dictator's Shadow; An Outsider's Guide To Julia Packages; Nobody gives a shit; Lepton Decay Irregularity; An Easier Way; Julia's BinDeps (aka How To Install Cairo); Good Example Of Good Police Work (And Anonymity Being Hard); Best Santiago Burgers; Also; Michael Emmerich (Vibrator Translator) Interview (Japanese Books); Clarice Lispector (Brazillian Writer); Books On Evolution; Looks like Ara (Modular Phone) is dead; Index - Translations From Chile; More Emotion in Chilean Wines; Week 7; Aeon Magazine (Science-ish); QM, Deutsch, Constructor Theory; Interesting Talk Transcripts; Interesting Suggestion Of Election Fraud; "Hard" Books; Articles or Papers on depolarizing the US; Textbook for "QM as complex probabilities"; SFO Get Libor Trader (14 years); Why Are There Still So Many Jobs?; Navier Stokes Incomplete; More on Benford; FBI Claimed Vandalism; Architectural Tessellation; Also: Go, Blake's 7; Delusions of Gender (book); Crypto AG DID work with NSA / GCHQ; UNUMS (Universal Number Format); MOOCs (Massive Open Online Courses); Interesting Looking Game; Euler's Theorem for Polynomials; Weeks 3-6; Reddit Comment; Differential Cryptanalysis For Dummies; Japanese Graphic Design; Books To Be Re-Read; And Today I Learned Bugs Need Clear Examples; Factoring a 67 bit prime in your head; Islamic Geometric Art; Useful Julia Backtraces from Tasks; Nothing, however, is lost with less discomfort than that which, when lost, cannot be missed; Article on Didion; Cost of Living by City; British Slavery; Derrida on Metaphor; African SciFi; Traits in Julia; Alternative Japanese Lit; Pulic Key as Address (Snow); Why Information Grows; The Blindness Of The Chilean Elite; Some Victoriagate Links; This Is Why I Left StackOverflow; New TLS Implementation; Maths for Physicists; How I Am 8; 1000 Word Philosophy; Cyberpunk Reading List; Detailed Discussion of Message Dispatch in ParserCombinator Library for Julia; FizzBuzz in Julia w Dependent Types; kokko - Design Shop in Osaka; Summary of Greece, Currently; LLVM and GPUs; See Also; Schoolgirl Groyps (Maths); Japanese Lit; Another Example - Modular Arithmetic; Music from United; Python 2 and 3 compatible alternative.; Read Agatha Christie for the Plot; A Constructive Look at TempleOS; Music Thread w Many Recommendations; Fixed Version; A Useful Julia Macro To Define Equality And Hash; k3b cdrom access, OpenSuse 13.1; Week 2

© 2006-2015 Andrew Cooke (site) / post authors (content).

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.


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 =

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