… of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides …index be used having an index on one column and using order by on another column. do i need to add the index on column… selecting using some date ranges? or between dates? or after a date? A: Date comparisons work …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_indexes view: mysql> select * from all_indexes\G … *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN_CNT: 3 COLUMN_NAMES: rental_date,inventory_id,customer_id index_total_pages: 27 index_total_size…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran this… to force MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX(uid… bizarre. On one hand, it seems like MySQL is using date windowing for two-digit dates (this would explain 2024-06-21), but…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_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: 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: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 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: 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…
