…_orderdate >= ’1993-08-01′ and o_orderdate < date_add( ’1993-08-01′ ,interval ’3′ month) … workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output… 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: 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… different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order you will not… “reverse_date” column and using it for sort. With MySQL 5.0 you even can use triggers to update it as real date updates…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how… data from the index. MySQL can ether read index only for all rows, in this case you will see “Using Index” in EXPLAIN output… probably use date (or at least year) or birth instead of age, and put zip as first column in the index as it…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and variable…_show_collationsblogpercona.commanual Com_show_column_typesblogpercona.commanual Com_show_contributorsblogpercona…tmp_tablesblogpercona.commanual datadirblogpercona.commanual date_formatblogpercona.commanual datetime_formatblogpercona.commanual…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… date range. A very inefficient approach! MySQL was unable to optimize those simple conditions to match both product id and date range by index… introducing additional DATE column and using it for filtering instead: ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX itm_prd…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…, the test data was hashed over the values in the date_id column. There will be another blog post about the usage… on disk: 42GB (with indexes) # du -sh * 203M ibdata1 128M ib_logfile0 128M ib_logfile1 988K mysql 2.1G ontime1 2.1G… is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE: name employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id 26, fields 2/6, uniq… pages 6120 FIELDS: emp_no from_date DB_TRX_ID DB_ROLL_PTR salary to_date INDEX: name emp_no, id 27, fields…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… width: create table ads_by_day_by_blueness ( day date not null, ad int unsigned not null, clicks int… will share a set of primary key columns. And not only will every index be a little narrower, the table… that work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, doing…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even… direct column value needs to do the filesort: mysql> explain select * from tst where i=5 and date(d)=date(now()) order by date(d…
