… (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (Please provide specific… you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or… main use case for PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… but there are generally other considerations to take into account. He also didn’t answer the ‘Insert’ part…could easily be quicker on a partitioned table, as Mysql can fire off one thread per partition (at … saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do …
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… UNIONs into a subquery and then doing the SELECT INTO OUFILE from this. Using the employees.employees table: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * INTO OUTFILE… bear, otherwise SELECT .. UNION SELECT INTO OUTFILE should be convenient. Lastly to demonstrate the purpose of this post. mysql [localhost] {msandbox} (employees) > SELECT * -> FROM employees…
Post: How Percona diagnoses MySQL server stalls
March 3, 2011 By 6 Comments
…. Finally, make sure that the script can log into MySQL (if necessary, put a password into .my.cnf, or in the MYSQLOPTIONS variable…_02_11_33_59 2011_03_02_15_10_03 Select a timestamp from the list: 2011_03_02_15_10…. There are many things that can cause a stall in MySQL, and they usually begin microscopically and get worse over time…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3… with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then RENAME old… COLUMN c; session7 > select * from test_2 order by id; session8 > select * from test_2 order by id; session9 > select * from test_2…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a… output: mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…AUR repository), however if you do happen to run into issues, our support service engineers are happy to provide…, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row *************************** Success!: Hello…
Post: Profiling MySQL stored routines
January 19, 2009 By 4 Comments
… into play. Let me show you how this works: mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT whatstheweatherlike…set (0.00 sec) mysql> show profiles; +———-+————+————————————————————-+ | Query_ID | Duration | Query | +———-+————+————————————————————-+ | 1 | 0.00005100 | SELECT CURTIME() into time | | 2 | 0…
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… goes into a lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL… may apply, for example you may benefit to convert BETWEEN into IN-ranges in some cases for better index usage. Q…
