…) mysql> select * from tst for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for UPDATE and LOCK IN SHARE MODE sees it. What is happening ? SELECT for UPDATE… assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW…); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… not locking rows in source table other transaction could modify the row and commit before transaction which is running INSERT .. SELECT statement. This…. Locking rows in the source table while reading them protects from this effect as other transaction modifies rows before INSERT … SELECT had chance… can see INSERT… SELECT has a lot of lock structs, which means it has locked a lot of rows. “fetching rows” of course…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… EXPLAINs: mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5; +—-+————-+————+——+—————+——+———+——-+——-+————-+ | id | select_type | table… 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +—-+————-+————+——+—————+——+———+——-+——+————-+ | id | select_type…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
… single “seed” row into the table: INSERT INTO repl.t1 VALUES (); Query OK, 1 row affected (0.00 sec) INSERT … SELECT a few times to get some dummy data: insert into repl.t1 select NULL from t1; Query OK, 1 row affected…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… ref: const,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force…=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible_keys: cat…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… | NULL | | +—-+————–+————–+——-+—————+———–+———+——+——+————-+ 5 rows in set (0.00 sec) Compared to using UNION simply: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * -> FROM employees -> WHERE… you think the number of resulting rows would be to big to bear, otherwise SELECT .. UNION SELECT INTO OUTFILE should be convenient. Lastly…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is that, select rows from first table participating in the joins are read, and then for each of these rows an index lookup… was introduced. When BKA is being used then, after the selected rows are read from table1, the values of indexed columns that… be joined to table t2, then selected rows from t1 would be read and then for all rows, index lookup would be performed…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…: SELECT GENDER, COUNT(*) FROM PEOPLE GROUP BY GENDER This query will return only couple of rows but it is clear all rows from… with couple of rows in it) Now if we have the same table with no indexes and query SELECT GENDER, COUNT(*) FROM… track the future of individual row. Queries with temporary result set are especially complicated, for example: SELECT * FROM (SELECT COUNTRY,COUNT(*) FROM PEOPLE…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
… there is considerable effort needed to select rows for modification – spread it out and have separate select and update queries. In such case… allows it. In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but it will… when there are much more rows examined than modified. The next common mistake is using INSERT … SELECT – which is in similar to…
