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

Last 100 entries

Ky - Restaurant Santiago; The Black Dork Lives!; The UN Requires Unaninmous Decisions; LPIR - Steganography in Practice; How I Am 6; Clear Explanation of Verizon / Level 3 / Netflix; Teenage Girls; Formalising NSA Attacks; Switching Brakes (Tektro Hydraulic); Naim NAP 100 (Power Amp); AKG 550 First Impressions; Facebook manipulates emotions (no really); Map Reduce "No Longer Used" At Google; Removing RAID metadata; New Bike (Good Bike Shop, Santiago Chile); Removing APE Tags in Linux; Compiling Python 3.0 With GCC 4.8; Maven is Amazing; Generating Docs from a GitHub Wiki; Modular Shelves; Bash Best Practices; Good Emergency Gasfiter (Santiago, Chile); Readings in Recent Architecture; Roger Casement; Integrated Information Theory (Or Not); Possibly undefined macro AC_ENABLE_SHARED; Update on Charges; Sunburst Visualisation; Spectral Embeddings (Distances -> Coordinates); Introduction to Causality; Filtering To Help Colour-Blindness; ASUS 1015E-DS02 Too; Ready Player One; Writing Clear, Fast Julia Code; List of LatAm Novels; Running (for women); Building a Jenkins Plugin and a Jar (for Command Line use); Headphone Test Recordings; Causal Consistency; The Quest for Randomness; Chat Wars; Real-life Financial Co Without ACID Database...; Flexible Muscle-Based Locomotion for Bipedal Creatures; SQL Performance Explained; The Little Manual of API Design; Multiple Word Sizes; CRC - Next Steps; FizzBuzz; Update on CRCs; Decent Links / Discussion Community; Automated Reasoning About LLVM Optimizations and Undefined Behavior; A Painless Guide To CRC Error Detection Algorithms; Tests in Julia; Dave Eggers: what's so funny about peace, love and Starship?; Cello - High Level C Programming; autoreconf needs tar; Will Self Goes To Heathrow; Top 5 BioInformatics Papers; Vasovagal Response; Good Food in Vina; Chilean Drug Criminals Use Subsitution Cipher; Adrenaline; Stiglitz on the Impact of Technology; Why Not; How I Am 5; Lenovo X240 OpenSuse 13.1; NSA and GCHQ - Psychological Trolls; Finite Fields in Julia (Defining Your Own Number Type); Julian Assange; Starting Qemu on OpenSuse; Noisy GAs/TMs; Venezuela; Reinstalling GRUB with EFI; Instructions For Disabling KDE Indexing; Evolving Speakers; Changing Salt Size in Simple Crypt 3.0.0; Logarithmic Map (Moved); More Info; Words Found in Voynich Manuscript; An Inventory Of 3D Space-Filling Curves; Foxes Using Magnetic Fields To Hunt; 5 Rounds RC5 No Rotation; JP Morgan and Madoff; Ori - Secure, Distributed File System; Physical Unclonable Functions (PUFs); Prejudice on Reddit; Recursion OK; Optimizing Julia Code; Cash Handouts in Brazil; Couple Nice Music Videos; It Also Works!; Adaptive Plaintext; It Works!; RC5 Without Rotation (2); 8 Years...; Attack Against Encrypted Linux Disks; Pushing Back On NSA At IETF; Summary of Experimental Ethics; Very Good Talk On Security, Snowden; Locusts are Grasshoppers!; Vagrant (OpenSuse and IDEs)

© 2006-2013 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