…in set (1.01 sec) mysql> explain select * from idxitest where a=100 order by id desc limit 1; +—-+————-+———-+——-+—————+———+———+——+——+————-+ | id | select_type | table…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…mysql.bkbits.net/mysql-5.1-falcon for Falcon (Please note this is a first release of Falcon and it is still in…Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d READ_KEY_RANGE_NO_DATA_LIMIT Query: SELECT city FROM $tableName WHERE country_id…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…mysql- –with-innodb Method of benchmark: 1. Prepare table…Query: SELECT name FROM $tableName WHERE id = %d READ_KEY_POINT Query: SELECT name FROM $tableName WHERE country_id = %d READ_KEY_POINT_LIMIT Query: SELECT name FROM $tableName WHERE country_id…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…queries do something like SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL…select * from table where…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…selective. So I tried this: select * from table where id in (SELECT id FROM `table` WHERE (col1=’A'||col1=’B')) ORDER BY id DESC LIMIT 20 OFFSET 0; In this case we forcing MySQL…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… problems with loosing data etc. Read more in my Why MySQL Could be slow with Large Tables article. 5. The n+1 pattern… previous query. For example you can do: SELECT id FROM users WHERE featured=1; Now populate List for IN on your PHP application: SELECT * FROM articles WHERE user_id IN(23…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…In practice – it will not: mysql> explain SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347); +—-+————-+——–+——-+—————+——+———+——+——-+————-+ | id | select_type | table…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query…queries which you want to have cached – such as SELECT SQL_CACHE col from foo where id=5. If you run in…
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…Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where…
