… presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed… the great questions. I answered as many as we had time for during…t the primary key solution for random selection only work when the IDs … to add a column to the table, and populate the rows you want to …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…do queries on any other column, these could easily be quicker on a partitioned table, as Mysql can fire off … saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do … and quicker than deleting a few hundred thousand rows… for example, if you need to delete. However, …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P.TABLE_SCHEMA, ‘.’, P.TABLE_NAME, ‘.’, P.COLUMN… cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY …
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
…as of MySQL 5.5 it contains only variable name and value. Yet I would suggest adding few more columns such as… select: mysql> select @@global.sort_buffer_size; +—————————+ | @@global.sort_buffer_size | +—————————+ | 2097152 | +—————————+ 1 row in…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in… in this case each selecting about 1% rows from this table which contains about 10M rows. mysql [localhost] {msandbox} (test) > explain select avg(length(val…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… > select * from test where id=1; +—-+——+ | id | x | +—-+——+ | 1 | foo | +—-+——+ 1 row in set (0.00 sec) session2 > ALTER TABLE test add column… rows in set (0.00 sec) session6 > ALTER TABLE test_2 DROP COLUMN c; session7 > select * from test_2 order by id; session8 > select… was never an issue when the application was using MySQL version < 5.5 as the datastore. There was another interesting case this…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… contain primary key columns) For each pk_column value in step 1 do: SELECT non_key_column FROM tbl WHERE pk_column=val As you can… them by pk_column, and do a range primary key lookup as follows: SELECT non_key_column from tbl WHERE pk_column IN (…) As you can… reading more rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…for new connections as in MySQL. This is very helpful for measurement as otherwise we might …_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from…values reported in the 95% (95th percentile) column as that gives us more accurate understanding. Now…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
…row in set (3.26 sec) mysql [localhost] {msandbox} (test) > explain select max(t1+0) from t99v1; +—-+————-+——-+——-+—————+——+———+——+———-+————-+ | id | select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row…
