… ( channel_id Int NOT NULL, date_time Datetime NOT NULL, value Double NOT NULL, Primary Key (channel_id, date_time) ); too slow (more then a few seconds when thousands of records): select min(date_time) from tabData where id…(date_time) as m from tabData where channel_id = 1 union select min(date_time) from tabData where channel_id = 2 union select min(date_time…
Comment: Multi Column indexes vs Index Merge
October 20, 2009 By 31 Comments
… over 80,000,000 rows containing GPS information latitude, longitude, date_time, vehicle_id and a few more columns. I can’t… of results for a vehicle. Tried (date_time,vehicle_id) and MYSQL won’t use it, (date_time) and its bringing back thousands of rows under explain. Using innodb. Inserting up to 100 per second. Query select g.date_time…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
… employees set age = ((YEAR(CURDATE())-YEAR(birth_date)) – (RIGHT(CURDATE(),5)
Comment: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… to be on the extremes, e.g. all-zero dates or dates like you mentioned. Probably not too hard to find them… Toolkit tool could iterate through all tables, look for indexed date/time columns, and check the first and last to see if…
Comment: The perils of uniform hardware and RAID auto-learn cycles
November 17, 2010 By 16 Comments
…: WARNING Date/Time: 12-09-2010 Additional Info : Cache battery 0 in controller 0 is Degraded (Non-Critical) [probably harmless] And normally: Date/Time…
Post: Replication of the NOW() function (also, time travel)
November 28, 2012 By 6 Comments
…-01′); SELECT NOW(),SYSDATE(); Notice that SYSDATE returns the correct date. This means the SYSDATE() function is not replication safe by…
Comment: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… – you may shard on “user” and when use partitioning by date/time to separate new data from old.
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
…, with some additional converters so we can match against various date/time columns as well.
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_pool_stats side by side: INNODB_OLD_BLOCKS_TIME=0 INNODB_OLD_BLOCKS_TIME=1000 *************************** 1. row ************************************************ 1. row ******* POOL_ID: 0… innodb_old_blocks_time=0: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder on…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… projection, instead replacing them with the dimension keys: select dim_date.date_id, origin.airport_id as origin_airport_id, dest.airport… list of the queries, followed by a response time table recording the actual response times for each query. The queries should be… on ( origin_airport_id = origin.airport_id) JOIN dim_date using (date_id) JOIN dim_flight using (flight_id) WHERE Year BETWEEN…
