…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes…date…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… a lot larger problems with MySQL 5.5 with addition of user variables, stored… simple script: while true do date >> ps.log ps aux | grep mysqld …mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index…indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… which with other memory needs leaves roughly 1GB for OS Cache. Here is pt-summary snippet: # Percona Toolkit System Summary Report ###################### Date… be better as it has less overhead with double buffering as well as more information MySQL has about data which will be accessed next – in case of full table scan, index scans etc the…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… see above, MySQL 5.6 outperformed MySQL 5.5.30 with the big buffer pool. Why is the MySQL 5.6.10 with default settings… buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer…) And with innodb_old_blocks_time=0: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… in the list now so index on (category_id, date_created) can’t be used directly. Index on date_created separately would still work… simply indexes column. Note: In some cases even if it is possible to use index to do ORDER BY with JOIN MySQL still will… long as it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…using one of these foreign key index with low cardinality and somewhat even …MySQL will create the following indexes: unique index on order_id index on item_type_id index on locale_id index on currency_id index… index on order_date to help with finding orders for a given date. 8 columns 6 indexes…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… – these are features MySQL should get for all storage engines at some point. Descending indexes – This is something MySQL does not have at… “reverse_date” column and using it for sort. With MySQL 5.0 you even can use triggers to update it as real date updates… be using the index (rows with all C values will be retrieved from the index) and if this is not the index covered query…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… size on disk: 42GB (with indexes) # du -sh * 203M ibdata1 128M ib_logfile0 128M ib_logfile1 988K mysql 2.1G ontime1 2.1G…` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year…=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G *************************** 1…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…, `l_linestatus` char(1) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` char(25) NOT NULL… what is ‘Using Index condition’, what I was able to find is Baron’s bug with no mention in MySQL 6.0 manual… to mention here is that MySQL is wrong choosing index scan here. If we try to execute query with IGNORE KEY (li_shp…
