What makes for a true statement? We usually test statements using a WHERE clause: SELECT * FROM world.City WHERE Population > 1000000 The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF(): SET @val := 7; SELECT IF(@val > 2, [...]
In my previous post I've shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema. In this part I present DBA's handy syntax for schema and table operations and maintenance. Confession: while I love INFORMATION_SCHEMA's power, I just hate writing queries against it. It's just so much typing! Just getting the [...]
A new routine is now available in common_schema, which makes for an easier execution syntax for some operations: foreach(collection_to_iterate_over, queries_to_execute_per_iteration_step); To illustrate what it can do, consider: call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPACT'); call $('schema like shard_%', 'CREATE TABLE ${schema}.messages (id INT)'); call $('2000:2009', 'INSERT IGNORE INTO report (report_year) VALUES (${1})'); $() [...]
SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side. On server side, that is, from within MySQL itself, one cannot: SELECT `Database` FROM (SHOW DATABASES); One cannot: DECLARE show_cursor CURSOR FOR SHOW [...]
Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes: UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA' UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA" This makes for JavaScript- or Python-style quoting: you quote [...]
Recap on the problem: A query takes a long time to complete. During this time it makes for a lot of I/O. Query's I/O overloads the db, making for other queries run slow. I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the [...]
What's all this about? I've you've been to my talk in London, then you've already got this as homework. If not, allow me to fill in the details: I was speaking about MySQL's programmatic nature in many aspects (best if you read the slides!). We discussed user defined variables, derived tables, ordering constructs, order of [...]
Here are the slides from my talk Programmatic Queries: things you can code with SQL held on October 25th on the Percona Live event, London. Programmatic Queries: PDF I wish to thank those who attended my talk. The topic of the talk was irregular and, to some extent, controversial. Should one really rely on internal [...]
I'll be speaking at the Percona Live event, held in London, October 24, 25, 2011. My session is called Programmatic Queries: things you can code with SQL. It's a short 30 minute talk, in which I present underlying knowledge of the programmatic nature of SQL queries within MySQL, and how to take advantage of such [...]
It's another new year by Jewish calendar. And what do I wish for in the following year? World peace Good health to all Have some way to turn SHOW commands into SELECT statements, server side. I'm fervently trying to hack around this. Stored routines, export/import from file, text manipulation, I don't care! I want to [...]