Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static system. Commented out will work but just wondering if 0 or some other value would make it not present? >Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave. >If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1. -https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_master-data My plan was to use: mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=0 ... but from the above entry that would put the CHANGE MASTER TO as an uncommented command.

I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup. I have set the below configuration: 1. ROW based replication is set. 2. Transaction Isolation is set to read-committed. Today, there was a insert going on in my Master. It was in the format INSERT INTO table1 SELECT * FROM table2 Table 2 has 200 million rows. Though the number of insert records was only 5000 but the operation lasted for 30 mins. I observed replication lag during the insert operation. I have load infile disabled due to security concerns. Hence I can't insert using that as well. I went this article from Percona which says that this can be resolved if txn isolation is used as ROW and versions above 5.1 that this is resolved. 1. In what way I can make my slave to be in sync with Master in such conditions? 2. Why does the slave lag here?

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;ibdata4:10000M;ibdata5:10000M:autoextend innodb_buffer_pool_instances = 3 innodb_buffer_pool_size = 3G innodb_additional_mem_pool_size = 8M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_additional_mem_pool_size = 128M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 100 innodb_file_per_table innodb_flush_method=O_DIRECT Still every time my alter query alter table user add unique index idx_emailHash (emailHash); giving Table 'user' is full? What am I missing. I am using MySQL 5.6 Some more info [root@db data]# ll | grep user -rw-rw----. 1 mysql mysql 19551 Jun 10 14:33 user.frm -rw-rw----. 1 mysql mysql 28412215296 Jun 10 22:58 user.ibd [root@db data]# ll | grep ibd -rwxr-xr-x. 1 mysql mysql 60021538816 Jun 10 22:58 ibdata1 -rw-rw----. 1 mysql mysql 314572800 Jun 10 22:20 ibdata2 -rw-rw----. 1 mysql mysql 31457280000 Jun 10 22:33 ibdata3 -rw-rw----. 1 mysql mysql 10485760000 Jun 10 22:51 ibdata4 -rw-rw----. 1 mysql mysql 10485760000 Jun 10 22:51 ibdata5

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available; * OS Debian 9; * Mysql 5.6.47; To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed. I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE MALICIOUS_TABLE (
  column_1 int(11) NOT NULL AUTO_INCREMENT,
  column_2 varchar(8) NOT NULL,
  column_3 datetime NOT NULL,
  column_4 int(11) NOT NULL,
  column_5 int(11) DEFAULT NULL,
  column_6 int(11) DEFAULT NULL,
  column_7 int(11) DEFAULT NULL,
  column_8 tinyint(1) DEFAULT NULL,
  column_9 datetime DEFAULT NULL,
  column_10 int(11) DEFAULT NULL,
  column_11 varchar(2048) DEFAULT 'column_11',
  column_12 tinyint(1) DEFAULT NULL,
  column_13 datetime DEFAULT NULL,
  column_14 tinyint(1) DEFAULT NULL,
  PRIMARY KEY (column_1),
  KEY fk_ual_aut_idx (column_2),
  KEY fk_aul_c_idx (column_4),
  KEY kf_ual_po_idx (column_5),
  KEY fk_ual_ute_idx (column_10),
  KEY column_1 (column_1),
  KEY column_2 (column_2),
  CONSTRAINT fk_aul_c FOREIGN KEY (column_4) REFERENCES t_table2 (column_4) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_aut FOREIGN KEY (column_2) REFERENCES t_tabl3 (column_2) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_po FOREIGN KEY (column_5) REFERENCES t_table4 (column_5) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_ute FOREIGN KEY (column_10) REFERENCES t_table5 (column_10) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from MALICIOUS_TABLE;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time. The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no. Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself? Thank you in advance.

Today I had an issue where I accidentally deleted a whole table (which cascaded to a few other tables) due to a funky piece of MySQL syntax, see here . Luckily I had a backup from midnight, however all data was lost between midnight and to when I deleted all the rows in my table. How can I better back up my database to be more up to date with the live system, or perhaps better just lagging a hour behind it? I've seen people mention replication and binary logs but this is all kind of new to me. From my understanding, having a replication setup, deleting my table in the master would also delete in the slave database? Would I be better off trying to take hourly differential backups? Or should i just do full backups every hour? Our database is tiny, less than a gig, and we have room so it's feasible, but I would rather do things a proper way that would work for larger databases as well.

This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is running.Altered table is getting dumped with altered structure but DATA is missing.I repeated this test 3 times and same result is there. Just wanted to know this is the way it works? Please give some explanation. Thanks in advance.

Reason for lot of small relay log file formation. I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.

I tried to install MySQL on my machine (win 8 64 bit). I downloaded mysql-5.6.24-winx64.exe and after completing the MySQL 5.6 setup wizard, MySQL Server Instance Configuration Wizard does not start and nothing happens. In the Windows start menu for MySQL I have only 2 options: - MySQL 5.6 command line client - MySQL 5.6 command line - Unicode. So what can I do to resolve this problem and run MySQL on my machine.

I have to tables with 65.5 Million rows: 1) CREATE TABLE RawData1 ( cdasite varchar(45) COLLATE utf8_unicode_ci NOT NULL, id int(20) NOT NULL DEFAULT '0', timedate datetime NOT NULL DEFAULT '0000-00-00 00:00:00', type int(11) NOT NULL DEFAULT '0', status int(11) NOT NULL DEFAULT '0', branch_id int(20) DEFAULT NULL, branch_idString varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (id,cdasite,timedate), KEY idx_timedate (timedate,cdasite) ) ENGINE=InnoDB; 2) Same table with partition (call it RawData2) PARTITION BY RANGE ( TO_DAYS(timedate)) (PARTITION p20140101 VALUES LESS THAN (735599) ENGINE = InnoDB, PARTITION p20140401 VALUES LESS THAN (735689) ENGINE = InnoDB, . . PARTITION p20201001 VALUES LESS THAN (738064) ENGINE = InnoDB, PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB); I'm using the same query: SELECT count(id) FROM RawData1 where timedate BETWEEN DATE_FORMAT(date_sub(now(),INTERVAL 2 YEAR),'%Y-%m-01') AND now(); 2 problems: 1. why the partitioned table runs longer then the regular table? 2. the regular table returns 36380217 in 17.094 Sec. is it normal, all R&D leaders think it is not fast enough, it need to return in ~2 Sec. What do I need to check / do / change ? Is it realistic to scan 35732495 rows and retrieve 36380217 in less then 3-4 sec?

Using Aurora Serverless, when attempting to alter table mytable add column mynewcolumn decimal(10,3) after myoldcolumn I receive "ERROR 1114 (HY000): The Table 'mytable' is full". The table has hundreds of millions of rows. Normally this error results from a full partition, but since I'm using Aurora Serverless, I can't manually provision partition space. What should I do to avoid this error?

I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version. I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, I will map the new installation data directory to the older data directory which is used by MySQL 5.1. After this, I will upgrade using mysql_upgrade.exe. Will this plan work out or not ? Also let me know if any precautions to be taken care. Edit: This is production server and the DB is of huge size 100 GB, so we are not able to go with backup and restore mechanism which is time consuming process.

I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge delete operation on this table. And daily archiving some old data. Is this because of that? Optimize table - command is the only way to reclaim the space?

We have recently upgraded to mysql5.6.25 from mysql5.5.x/mysql5.1.x on our mysql-cluster. Below is a brief snapshot of our architecture. Since we have upgraded and enabled gtid-mode we have been intermittently getting slave errors similar to : ***Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'd7e8990d-3a9e-11e5-8bc7-22000aa63d47:1466'.' on query. Default database: 'adplatform'. Query: 'create table X_new like X'*** Our observations are as below.. - These slave errors are resolved simply by restarting the slave. - Such errors are always with Create/Drop of tables which have Memory Storage Engine. - Errors on Complete-Slave(B) show up continuously at a fixed minute (39th) of the hour and have been repeating since we have upgraded, almost a week. - Errors on Complete-Slave as well as Partial slave are observed whenever its master is restarted. - Cluster-1 and Cluster-2 have centos machines and Cluster-3 have ubuntu-machines. Slaves on centos machines also fail with the same error whenever its master(C/D) is restarted, but slave on ubuntu machines do not fail!!. We have temporarily been able to live with this issue by setting up an action-script on our monitoring system which fires on slave error alert on any machine. A look into gtid_next section in replication-options doc of mysql tells following > Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not > AUTOMATIC, DROP TABLE did not work correctly when used on a > combination of nontemporary tables with temporary tables, or of > temporary tables using transactional storage engines with temporary > tables using nontransactional storage engines. In MySQL 5.6.20 and > later, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error > when used with either of these combinations of tables. (Bug #17620053) This seems related to my issue but still doesn't not explain my scenario. Any hints/direction to solve the issue would be greatly appreciated... **EDIT :** I managed to find a similar recently reported bug in mysql(#77729), description of which is as follows : https://bugs.mysql.com/bug.php?id=77729 > When you have table with Engine MEMORY working on replication master, > mysqld injects "DELETE" statement in binary logs on first access query > to this table. This insures consistency of data on replicating slaves. > > If replication is GTID ROW based, this inserted "DELETE" breaks > replication. Logged event is in STATEMENT format and do not generate > correct SET GTID_NEXT statements in binary log. Unfortunately, the status of this bug is marked as Can't Repeat...

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would equal January Total + February Total + March Total. My issue is that my query is showing the SUM() of the Total for all months. This is DDL that illustrates my issue

Create Table empSales
(
    spendType varchar(100)
    ,spendAmt decimal(10,2)
    ,spendMonth varchar(100)
);

Insert Into empSales (spendType, spendAmt, spendMonth ) VALUES
('James', '1.00', 'January'),
('Richard', '3.28', 'January'),
('Barb', '4.13', 'January'),
('James', '3.00', 'February'),
('Richard', '3.28', 'February'),
('Barb', '4.13', 'February'),
('James', '2.00', 'March'),
('Richard', '5.28', 'March'),
('Barb', '7.13', 'March');

And this is the query I tried that only gives the overall total instead of just the YTD total I'm after

with MonthAgg as (
select IFNULL(spendType,'Total') spendType 
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February 
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April 
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August 
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November 
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select spendMonth , Sum(spendAmt) Amt 
FROM empSales
GROUP BY spendMonth
)
,mycteYTD as (
select spendMonth , sum(Amt) Over(order by Cast(spendMonth+ ' 01, 1900' as date) ) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'YTD' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD 
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD 
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD 
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD 
, max(Case when spendMonth='May' then YTD else null end ) MayYTD 
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD 
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD 
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD 
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD 
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD 
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD 
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD 
from mycteYTD

Why doesn't this MySQL 5.6 stored procedure compile with MySQL Workbench? CREATE PROCEDURE spDataLocationSetActive ( IN driveLetter varchar(1), IN active tinyint(1) ) BEGIN if( Select DriveLetter From DataLocations Where DriveLetter = driveLetter ) then UPDATE DataLocations SET Active = 1 WHERE DriveLetter = driveLetter; end if; END 'DriveLetter', 'varchar(128)', 'NO', 'PRI', '', '' 'Directory', 'varchar(200)', 'NO', '', 'SCR\\DATA\\', '' 'MaxUsage', 'smallint(6)', 'NO', '', '95', '' 'Sequence', 'smallint(6)', 'YES', '', NULL, '' 'ErrorMessage', 'varchar(255)', 'YES', '', NULL, '' 'DriveCapacity', 'bigint(20)', 'YES', '', NULL, '' 'DriveFreeSpace', 'bigint(20)', 'YES', '', NULL, '' 'Active', 'tinyint(1)', 'NO', '', '1', '' Here is the result of Show Create Table DataLocations. Table,"Create Table" DataLocations,"CREATE TABLE DataLocations ( DriveLetter varchar(128) NOT NULL DEFAULT '', Directory varchar(200) NOT NULL DEFAULT 'SCR\\DATA\\', MaxUsage smallint(6) NOT NULL DEFAULT '95', Sequence smallint(6) DEFAULT NULL, ErrorMessage varchar(255) DEFAULT NULL, DriveCapacity bigint(20) DEFAULT NULL, DriveFreeSpace bigint(20) DEFAULT NULL, Active tinyint(1) NOT NULL DEFAULT '1', InactiveCount smallint(6) NOT NULL DEFAULT '0', LastInactiveDateTime datetime NOT NULL DEFAULT '2001-01-01 00:00:00', Enabled tinyint(1) NOT NULL DEFAULT '1', LastEnabledDateTime datetime NOT NULL DEFAULT '2001-01-01 00:00:00', PRIMARY KEY (DriveLetter), KEY IX_DataLocations_DriveLetter_Active_Enabled (DriveLetter,Active,Enabled) ) ENGINE=InnoDB DEFAULT CHARSET=latin1" and here is the insert statement INSERT INTO scdvr.DataLocations (DriveLetter, Directory, MaxUsage, Sequence, ErrorMessage, DriveCapacity, DriveFreeSpace, Active, InactiveCount, LastInactiveDateTime, Enabled, LastEnabledDateTime) VALUES ('/', 'SCDVR/', 85, 1, 15, 80000000000, 1, 0, '2001-01-01 00:00:00', 1, '2001-01-01 00:00:00'); I was wondering how to fix its syntax. Thank you.

I want to replicate all update statement from Master as a Insert at Slave end. Is it possible like in SQL Server? If so, how? I want to do it because, if any record on Master is updating and if same record is not present at Slave end, then instead of Replication tends to fail, it should insert that record at Slave end. All insert on Master will act as Insert only at Slave end.

I have a Delete Statement which fails with Below Exception com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction It waits for 50 Seconds for Lock and gives Up, I wanted to know which all SQL Statements can lock a table. LOCK WAIT 6017 lock struct(s), heap size 376, 137376 row lock(s), undo log entries 22011 MySQL thread id 8242, OS thread handle 0x2b019e749700, query id 13811010 10.103.89.37 administrator updating DELETE from CONFIGSTOREQASTAGINGREL.EVENT_DETAILS where UPDATE_DATE < 20191013 AND COMPONENT_NAME = 'health' ------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 194 page no 9414 n bits 17 index GEN_CLUST_INDEX of table CONFIGSTOREQASTAGINGREL.EVENT_DETAILS trx id 38176269 lock_mode X waiting Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len=6; bufptr=0x2b01d5f29030; hex= 0000001ef5fb; asc ;; 1: len=6; bufptr=0x2b01d5f29036; hex= 0000024674c5; asc Ft ;; 2: len=7; bufptr=0x2b01d5f2903c; hex= af000004900110; asc ;; 3: len=30; bufptr=0x2b01d5f29043; hex= 333532613463313736333863613932393935336262653631646135306137; asc 352a4c17638ca929953bbe61da50a7; (total 32 bytes); 4: len=30; bufptr=0x2b01d5f29063; hex= 316663346166393133353164633731393062363833393134386335326535; asc 1fc4af91351dc7190b6839148c52e5; (total 32 bytes); 5: len=6; bufptr=0x2b01d5f29083; hex= 6865616c7468; asc health;; 6: len=30; bufptr=0x2b01d5f29089; hex= 36436c74773144627644656948474472756e774d4f362020202020202020; asc 6Cltw1DbvDeiHGDrunwMO6 ; (total 32 bytes); 7: len=4; bufptr=0x2b01d5f290a9; hex= 81341726; asc 4 &;; 8: len=4; bufptr=0x2b01d5f290ad; hex= 800003aa; asc ;; 9: len=16; bufptr=0x2b01d5f290b1; hex= 4f494d2d534552564943452020202020; asc OIM-SERVICE ;; 10: len=4; bufptr=0x2b01d5f290c1; hex= 80000000; asc ;; 11: len=0; bufptr=0x2b01d5f290c5; hex= ; asc ;; 12: len=30; bufptr=0x2b01d5f290c5; hex= 436f6e6e656374697669747920746f20536563757265204167656e742069; asc Connectivity to Secure Agent i; (total 168 bytes); 13: len=4; bufptr=0x2b01d5f2916d; hex= 5da441f8; asc ] A ;; I wanted to know what is Record Lock and when does that gets locked.I would very thankful if someone explains what is the meaning of above statements i got from SHOW INNODB ENGINE STATUS

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS from our DB: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-09-29 17:00:30 7f9eb7410700 *** (1) TRANSACTION: TRANSACTION 2842247584, ACTIVE 0 sec, thread declared inside InnoDB 4998 mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1184, 26 row lock(s) MySQL thread id 69517881, OS thread handle 0x7f9ee07bd700, query id 11929163563 cfsender01.cf.intela.local 192.168.20.116 cfsender01 updating *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... Notice that there is no SQL statement listed in transaction (1). Does anyone know why this might happen, or what the lack of a sql statement in the show engine innodb status would indicate about the transaction? FWIW, transaction (1) was chosen as the victim by MySQL and rolled back. Also, we're running MySQL Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618.

I am using MySQL 5.6. In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value.

UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), INTERVAL 2 WEEK)
WHERE state = 6;

There are only 205 records that have state =6 and 3,500 total records. After 5 minutes I canceled the query, made an index on the state column, and tried again. After 10 minutes I canceled that one. What is going on, is this a known issue with MySQL updating using a datetime calculation function or something? I worry because I know I will have to run similar type updates in the future and I can't have it take that long. The EXPLAIN statement I believe is telling me it IS using my index:

selectType   table     type   possible_keys     key        key_len    ref    rows   extra
SIMPLE       invoices  range  stateIndex        stateindex  2          const  205 using where

There were no warnings in my explain statement. The CREATE statement of my table

CREATE TABLE invoices (
  idx int(11) NOT NULL AUTO_INCREMENT,
  number varchar(255) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  parentSOId int(11) DEFAULT NULL,
  parentProjectId int(11) DEFAULT NULL,
  status int(1) DEFAULT '1',
  active int(1) DEFAULT '1',
  dateEntered varchar(45) DEFAULT NULL,
  dateDue varchar(45) DEFAULT NULL,
  individualId int(11) DEFAULT '-1',
  amount decimal(11,2) DEFAULT '0.00',
  margin decimal(11,2) DEFAULT '0.00',
  comment varchar(500) DEFAULT '',
  custContactId int(11) DEFAULT '-1',
  custBuyerId int(11) DEFAULT '-1',
  taxable int(11) DEFAULT NULL,
  taxAmount decimal(11,2) DEFAULT NULL,
  totalAmount decimal(11,2) DEFAULT NULL,
  paymentTerms int(11) DEFAULT '-1',
  type int(11) DEFAULT '-1',
  shipVia int(11) DEFAULT '-1',
  manTax int(1) DEFAULT '0',
  state tinyint(4) DEFAULT '0',
  sentToContNotNeeded int(1) DEFAULT '0',
  sentToAcctNotNeeded int(1) DEFAULT '0',
  twoWeekAlert datetime DEFAULT NULL,
  threeWeekAlert datetime DEFAULT NULL,
  PRIMARY KEY (idx),
  KEY invoiceNum (number),
  KEY invoiceName (name),
  KEY fk-listprojects_idx (parentProjectId),
  KEY soIdIndex (parentSOId),
  KEY stateInd (state),
  CONSTRAINT fk-listprojects FOREIGN KEY (parentProjectId) REFERENCES listprojects (idx) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3604 DEFAULT CHARSET=latin1;

Things I've also tried: Doing it by primary key ie WHERE idx IN (list of ids) Instead of using datetime, just using date since it is enough for my needs and using DATE_ADD(curDate(), INTERVAL 2 WEEK) I've tried both ways: NOW() with a datetime type and curDate() with a date type. Both had the same issue. Running it on a much stronger machine. Still the same issue. Luckily I was just told to not retroactively affect old records so I will only be doing one record at a time, which seemed to work. I am still very curious why this seems to take forever especially when I am the only connection on a dev database. My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.

SELECT IFNULL(state,'Total') state_value, COUNT(1) rowcount 
FROM invoices 
GROUP BY state WITH ROLLUP;

Results:

-1	14
0	3217
2	5
4	54
5	9
6	205
Total	3504

SHOW INDEX ON invoices results:

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months. My question is how to add the new column to calculate the differences (positive or negative) against same month from base year. I'm aware they are two questions in one but if the above question is not possible, it is possible to compare a given date range against current year/month. Please see the below table for a better overview of the question. This is the desired output.. The simple SELECT statement (without COUNT or SUM) is like this: SELECT dim_date.year, dim_date.month, fact.total_cost FROM fact INNER JOIN dim_date ON fact.date_key = dim_date.date_key WHERE dim_date.year BETWEEN 2009 AND 2011 For simplicity please have a look at below star schema. Thank you in advance for any help.