Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]
Join 6100 others and follow Sean Hull on twitter @hullsean. Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently. Start by looking at how you’re fetching information from your [...]
When MySQL gets a query, it is the job of the optimizer to find
the cheapest way to execute that query. Decisions include access
method (range access, table scan, index lookup etc), join order,
sorting strategy etc. If we simplify a bit, the optimizer first
identifies the different ways to access each table and calculate
their cost. After that, the join order is decided.
However, some access methods can only be considered after the join order has been decided and therefore gets special treatment in the MySQL optimizer. For join conditions, e.g. "WHERE table1.col1 = table2.col2", index lookup can only be used in table2 if table1 is earlier in the join sequence. Another class of access methods is only meaningful for tables that are first in the join order. An example is queries with ORDER BY ... LIMIT. Prior to MySQL 5.6.10 there was a bug in MySQL that made the optimizer choose inefficient execution plans for this query type. …
There are so many exciting new features in MySQL 5.6 that I almost don't know where to start. To mention a few, MySQL's multi-core scalability has been significantly improved to meet modern hardware, InnoDB has better index statistics, much better performance, and online ALTER, replication has multi-threaded slaves and …[Read more]
Pipe viewer is a command line tool which is used to monitor the throughput, display the estimated time of completion or to limit the transfer rate of a pipe (pipeline).
Install pipe viewer on Debian / Ubuntu with the following command.
apt-get install pv
On CentOS / Fedora / RedHat use the yum command to install pipe viewer
yum install pv
To use pipe viewer just insert the pv command between two processes to monitor the throughput of the pipe.
cat logfile.log.1 | pv | gzip -9 > logfile.log.1.gz 9,18MB 0:00:01 [ 9,1MB/s] [ <=>
Or limit the transfer rate of the pipe to a designated number of bytes.
cat logfile.log.1 | pv --rate-limit 100 | gzip -9 > logfile.log.1.gz 300B 0:00:03 [ 101B/s ] [ <=> ]
I have just seen Limiting table disk quota in MySQL by Shlomi
Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.
drop table if exists logs;[Read more]
create table logs (t mediumtext) engine=innodb;
drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota()
WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024)
THEN TRUE ELSE FALSE
A SELECT statement with COUNT returns the number of rows retrieved by the SELECT statement (see mysql select count). For performance reasons, the desired result is to limit that count. Including a LIMIT clause in the SELECT statement will not work since it only restricts the number of rows returned, which is always one. The […]
Dear reader, this is a challenge. How’s your MySQL prowess? You know about LIMIT: it cuts off the results at the specified number.
mysql>s; select actor_id from sakila.actor where actor_id % 5 = 0 limit 5; +----------+ | actor_id | +----------+ | 5 | | 10 | | 15 | | 20 | | 25 | +----------+ 5 rows in set (0.00 sec)
But that query actually accessed 25 rows. What if I want to say “return up to 5 rows, but don’t read any more than 20 rows to find them?”
Right now I’ve got the following:
mysql> select actor_id, @rows -> from actor, (select @rows := 0) as x where -> ((@rows := @rows + 1) <= 20) -> and actor_id % 5 = 0 -> limit 5; +----------+-------+ | actor_id | @rows | +----------+-------+ | 5 | 5 | | 10 | 10 | | 15 | 15 | | 20 | 20 | +----------+-------+ 4 rows in set (0.00 sec)
The …[Read more]