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

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

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

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; 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; From outside, the UK looks less than stellar; Huge Fonts in VirtualBox; Keen - Complex Emergencies; The Fallen of World War II; Some Spanish Fiction; Calling C From Fortran 95; Bjork DJ Set; Z3 Example With Python; Week 1; Useful Guide To Starting With IJulia; UK Election + Media; Review: Reinventing Organizations; Inline Assembly With Julia / LLVM; Against the definition of types; Dumb Crypto Paper; The Search For Quasi-Periodicity...; Is There An Alternative To Processing?; CARDIAC (CARDboard Illustrative Aid to Computation); The Bolivian Case Against Chile At The Hague; Clear, Cogent Economic Arguments For Immigration; A Program To Say If I Am Working; Decent Cards For Ill People; New Photo; Luksic And Barrick Gold; President Bachelet's Speech; Baltimore Primer; libxml2 Parsing Stream; configure.ac Recipe For Library Path; The Davalos Affair For Idiots; Not The Onion: Google Fireside Chat w Kissinger; Bicycle Wheels, Inertia, and Energy; Another Tax Fraud; Google's Borg; A Verion That Redirects To Local HTTP Server; Spanish Accents For Idiots; Aluminium Cans; Advice on Spray Painting; Female View of Online Chat From a Male; UX Reading List; S4 Subgroups - Geometric Interpretation; Fucking Email; The SQM Affair For Idiots; Using Kolmogorov Complexity; Oblique Strategies in bash; Curses Tools; Markov Chain Monte Carlo Without all the Bullshit; Email Para Matias Godoy Mercado; The Penta Affair For Idiots; Example Code To Create numpy Array in C; Good Article on Bias in Graphic Design (NYTimes); Do You Backup github?; Data Mining Books; SimpleDateFormat should be synchronized; British Words; Chinese Govt Intercepts External Web To DDOS github; Numbering Permutations; Teenage Engineering - Low Price Synths; GCHQ Can Do Whatever It Wants; Dublinesque; A Cryptographic SAT Solver; Security Challenges; Word Lists for Crosswords; 3D Printing and Speaker Design; Searchable Snowden Archive; XCode Backdoored; Derived Apps Have Malware (CIA); Rowhammer - Hacking Software Via Hardware (DRAM) Bugs; Immutable SQL Database (Kinda); Tor GPS Tracker; That PyCon Dongle Mess...; ASCII Fluid Dynamics; Brandalism; Table of Shifter, Cassette and Derailleur Compatability; Lenovo Demonstrates How Bad HTTPS Is; Telegraph Owned by HSBC; Smaptop - Sunrise (Music)

© 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.

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