I've got a (large) table where I perform a query on, using 3 fields within the WHERE. One of these simple fields comes with an index (to start dating ?), and I am searching for hits previously 3 several weeks. While it'll not be a fast query, minimal I really hope for may be the index about this date for use.

This really is my query:

SELECT id
FROM statsTable
WHERE 1
   AND ip            =  'ipgoeshere'
   AND anotherstring =  'Quite a long string goes here, something like this or even longer'
   AND `date`        >  DATE_ADD( NOW( ) , INTERVAL -3 MONTH ) 

And it is explain:

id  select_type table       type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      statsTable  ALL     date            NULL    NULL    NULL    4833721 Using where; Using filesort   

This can be a complete table-scan, the amount of rows is off due to INNODB-row-counting I suppose, but that is all em. This takes about thirty seconds.

Basically pressure the index like so, I recieve the expected result:

SELECT id
FROM statsTable FORCE INDEX (date)
WHERE 1
   AND ip            =  'ipgoeshere'
   AND anotherstring =  'Quite a long string goes here, something like this or even longer'
   AND `date`        >  DATE_ADD( NOW( ) , INTERVAL -3 MONTH ) 

Again, the explain:

id  select_type table       type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      statsTable  range   date            date    8       NULL    1120172 Using where

We now have 'only' millions of results, but this will get done "lighting" quick (as with, 3 seconds rather than 30).

The table:

CREATE TABLE IF NOT EXISTS `statsTable` (

  `id`            int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date`          datetime NOT NULL,
  `ip`            varchar(15) NOT NULL,
  `anotherstring` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `date` (`date`)

) ENGINE=InnoDB;

The strange factor is: I've this table running on another database too (running on the different server), and also the index Has been used for the reason that instance. I can not see what is the problem here. It is possible to setting I skipped? Or can it be another minor difference? Aside from the variations, I can not understand why above query wouldn't make use of the key.

I've run OPTIMIZE TABLE and, as @DhruvPathak recommended ANALYZE TABLE, however the explain still stays exactly the same. I additionally attempted an ALTER TABLE as recommended with a friend, to rebuild the index. No luck.

Run Evaluate TABLE once, and find out in the event that works well for fixing the option of the optimizer.

http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

This can also help : MySQL not using indexes with WHERE IN clause?

Are you able to try editing your query ?

Why it is possible to reduntant TRUE condition WHERE one in the query ?

Change

SELECT id
FROM statsTable
WHERE 1
   AND ip            =  'ipgoeshere'
   AND anotherstring =  'Quite a long string goes here, something like this or even longer'
   AND `date`        >  DATE_ADD( NOW( ) , INTERVAL -3 MONTH ) 

To

SELECT id
FROM statsTable
where  `date`        >  DATE_ADD( NOW( ) , INTERVAL -3 MONTH ) 
AND ip            =  'ipgoeshere'
AND anotherstring =  'Quite a long string goes here, something like this or even longer'

according to your query format, the perfect index ought to be on

ip, date

or

ip, date, anotherstring <-- this could be overkill

and

order by null <-- eliminate the file sort

lastly, it may be your another database consists of far lesser record