June 18, 2013

Post: MySQL caching methods and tips

April 4, 2011 By 5 Comments

… since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands can be… can be indexed appropriately for your queries. Using INSERT .. SELECT for summary tables The INSERT .. SELECT approach works best when there is some sort…

Filed Under: Insight for Developers, MySQL Tagged With: , , , ,

Post: SELECT LOCK IN SHARE MODE and FOR UPDATE

August 6, 2006 By 51 Comments

… different from normal SELECT statements. Here is simple example: SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst values(1); Query OK, 1 row affected (0.00 sec) SESSION2: mysql> begin ; Query OK… would be INSERT INTO MyISAMTable SELECT * FROM INNODBTable; – A lot of shared locks on Innodb table but no updates. Supporting MySQL hints “HIGH…

Filed Under: Insight for Developers Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… ft_min_word_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title, body) AGAINST…) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

… set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1…, `cnt` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; mysql> insert into search_set values (-2,1),(-3,1),(-10,1… (0.00 sec) mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select a.val, b.val…

Filed Under: MySQL

Post: Statement based replication with Stored Functions, Triggers and Events

December 16, 2011 By 4 Comments

… servers. Example: mysql> create trigger Copy_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i); mysql> insert into t… variables are replicated on the slave. Example: mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into t VALUES(@this_year) Binary Log: #111213…

Filed Under: MySQL

Post: MySQL Slow query log in the table

May 31, 2007 By 1 Comment

As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had… of similar structure with needed index and populate it with insert… select statement as you need it. One little gotcha which confused… to get new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time DESC LIMIT…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL… makes sense is collation specific. Q: ORDER By optimization issues: select * from table where A=xxx and B between 100 and… can slow down your inserts and make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in…

Filed Under: Insight for DBAs, MySQL

Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80

February 25, 2013 By 10 Comments

…here is how to get Percona Server for MySQL up and running: Insert the SD card into a slot on your …transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row *************************** Success!: Hello …

Filed Under: Hardware and Storage, Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , ,

Post: Checking the subset sum set problem with set processing

May 16, 2011 By Leave a Comment

… that I have a lot of numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10… in set (20.47 sec) Now insert a value which will cause our check to pass: mysql> insert into data (val) values (16); Query OK, 1 row affected (0.01 sec) mysql> SELECT val as `val…

Filed Under: MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… bypass the hard-to-scale database server for the initial insertion, because you can write CSV files with any programming language… server.  If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it’ll… save that work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, doing the…

Filed Under: Insight for Developers Tagged With: , , , ,
« Older Posts
Newer Posts »