| 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

Jenny Erpenbeck (Author); Dijkstra, Coins, Tables; Python libraries error on OpenSuse; Deserving Trump; And Smugness; McCloskey Economics Trilogy; cmocka - Mocks for C; Concept Creep (Americans); Futhark - OpenCL Language; Moved / Gone; Fan and USB issues; Burgers in Santiago; The Origin of Icosahedral Symmetry in Viruses; autoenum on PyPI; Jars Explains; Tomato Chutney v3; REST; US Elections and Gender: 24 Point Swing; PPPoE on OpenSuse Leap 42.1; SuperMicro X10SDV-TLN4F/F with Opensuse Leap 42.1; Big Data AI Could Be Very Bad Indeed....; Cornering; Postcapitalism (Paul Mason); Black Science Fiction; Git is not a CDN; Mining of Massive Data Sets; Rachel Kaadzi Ghansah; How great republics meet their end; Raspberry, Strawberry and Banana Jam; Interesting Dead Areas of Math; Later Taste; For Sale; Death By Bean; It's Good!; Tomato Chutney v2; Time ATAC MX 2 Pedals - First Impressions; Online Chilean Crafts; Intellectual Variety; Taste + Texture; Time Invariance and Gauge Symmetry; Jodorowsky; Tomato Chutney; Analysis of Support for Trump; Indian SF; TP-Link TL-WR841N DNS TCP Bug; TP-Link TL-WR841N as Wireless Bridge; Sending Email On Time; Maybe run a command; Sterile Neutrinos; Strawberry and Banana Jam; The Best Of All Possible Worlds; Kenzaburo Oe: The Changeling; Peach Jam; Taste Test; Strawberry and Raspberry Jam; flac to mp3 on OpenSuse 42.1; Also, Sebald; Kenzaburo Oe Interview; Otake (Kitani Minoru) move Black 121; Is free speech in British universities under threat?; I am actually good at computers; Was This Mansplaining?; WebFaction / LetsEncrypt / General Disappointment; Sensible Philosophy of Science; George Ellis; Misplaced Intuition and Online Communities; More Reading About Japan; Visibilty / Public Comments / Domestic Violence; Ferias de Santiago; More (Clearly Deliberate); Deleted Obit Post; And then a 50 yo male posts this...; We Have Both Kinds Of Contributors; Free Springer Books; Books on Religion; Books on Linguistics; Palestinan Electronica; Books In Anthropology; Taylor Expansions of Spacetime; Info on Juniper; Efficient Stream Processing; The Moral Character of Crypto; Hearing Aid Info; Small Success With Go!; Re: Quick message - This link is broken; Adding Reverb To The Echo Chamber; Sox Audio Tools; Would This Have Been OK?; Honesty only important economically before institutions develop; Stegangraphy via PS4; OpenCL Mess; More Book Recommendations; Good Explanation of Difference Between Majority + Minority; Musical Chairs - Who's The Privileged White Guy; I can see straight men watching this conversation and laffing; Meta Thread Defending POC Causes POC To Close Account; Indigenous People Of Chile; Curry Recipe; Interesting Link On Marginality; A Nuclear Launch Ordered, 1962; More Book Recs (Better Person)

© 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