Tag: MySQL
Categories
mysqlnd_ms – Replication and Load Balancing
The mysqlnd replication and load balancing plugin (mysqlnd_ms) adds easy to use MySQL replication support to all PHP MySQL extensions that use mysqlnd.
Categories
A quicker drop table within MySQL
Whilst a truncate table operation is underway a lock mutex around the table is created to ensure data consistency; if using innodb file per table the physical .ibd file must be removed which is a painfully slow operation under ext3.
Categories
Things to chase after
MySQL specific?
select make_set(255,’bit0′,’bit1′,’bit2′,’bit3′,’bit4′,’bit5′,’bit6′,’bit7′);
select (1,2,3)=(1,2,3);
Categories
SQL: selecting top N records per group
Based on a similar concept, combined with string walking, I now present a query which selects top-n records for each group, ordered by some condition. It will require no subqueries. It executes faster than its more conventional alternatives.
via SQL: selecting top N records per group | code.openark.org.
Categories
MySQL FULL OUTER JOIN
But the first two joins—the inner join, and the left exclusion join—are logically equivalent to a left outer join, so we can write:
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL;
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | a | NULL | NULL |
| 2 | b | 2 | b |
| 1 | a | NULL | NULL |
| NULL | NULL | 3 | c |
+------+------+------+------+
Why doesn’t MySQL implement FULL OUTER JOIN syntax for this? We don’t know.
via Common Queries Tree.
Categories
PURGE MASTER LOGS
PURGE MASTER LOGS TO ‘mydb-bin.0023’;
Then the binlog before ‘mydb-bin.0023’ will be deleted.
You may also do this:
PURGE MASTER LOGS BEFORE ‘2008-06-13 08:00:00’;