June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s…reinitializing the sequence after making certain insert/update/delete operations.  If you have a dataset that …keywords were returned in a different order from what you specify in the query? The…

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

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…insert a subtree, you’re inserting multiple nodes, and you want to …DELETE FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM…table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table…

Filed Under: Insight for Developers, MySQL

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

…. So what can you do to recover such a table ? There are multiple things which can get corrupted and I will be… can’t insert or delete any data (though you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg… deleted rows or dropped table. I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb corruptions and deleted…

Filed Under: Insight for DBAs Tagged With:

Post: Sysbench with support of multi-tables workload

April 29, 2011 By 4 Comments

…to sysbench support for workload against multiple tables ( traditionally it used only single table). It is available from launchpad source tree lp:sysbench . …select insert delete update_index update_non_index to support different OLTP modes. Most interesting: all scripts support –oltp-tables-count=…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… OPTIMIZE TABLE, and that we might have to run OPTIMIZE TABLE multiple times if we’ve had a lot of changes to the table…_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +—————————+———+ 10 rows in set (0.00 sec) mysql> delete from dir_test_innodb…

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

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…incremental refresh method uses table changelogs which are created by FlexCDC. The refresh algorithm computes changes in multiple transactions. After each …MV which use those aggregate functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between …

Filed Under: Insight for DBAs, MySQL

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… multiple keys are defined. Not for everyday use but still pretty cool. Addendum Following Peter’s comment, I added varchar and deleted…: 0 mysql> delete from test_concat_part where id = 44; Query OK, 1 row affected (0.00 sec) mysql> flush tables; Query OK…’t fix table with quick recovery: Found wrong number of deleted records myisamchk: error: Run recovery again without -q MyISAM-table ‘test_concat…

Filed Under: MySQL

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… so. > Using multiple tables gives some very important benefits – your data > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few… time to inserts, updates, and deletes but there are other benefits. Also – if you’re replicating from a master to a slave…

Filed Under: Insight for Developers Tagged With: , ,

Comment: Why MySQL could be slow with large tables ?

January 31, 2008 By 141 Comments

…_A FOREIGN KEY (A) REFERENCES ATable(A) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT key… (not joining them with other tables). Once again, I use multiple inserts into the table, with sets of 150… the best design for the table, taking in consideration what I want from it?! Could the INSERTs be …

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Query Cache

July 27, 2006 By 70 Comments

…. Table level granularity in invalidation – If table gets modification all queries derived from this table are … comes from modification statements which you can calculate by (Com_insert+Com_delete+Com_…caching systems can effectively use memory on multiple systems so there is no duplication. …

Filed Under: Insight for DBAs Tagged With:
« Older Posts