How to tell MySQL to use more indexes

I'm having a problem with a MySQL query which is too slow because the indexing doesn't work properly in my humble opinion.

I have a table which contains 7 indexables fields and 3 data blobs.

CREATE TABLE IF NOT EXISTS `superstrat` (
  `idStrategy` int(11) NOT NULL AUTO_INCREMENT,
  `strategy_date` datetime NOT NULL,
  `strategy_type` int(11) NOT NULL,
  `strategy_supertype` int(11) NOT NULL,
  `strategy_codes` varchar(40) NOT NULL,
  `strategy_vols` blob NOT NULL,
  `strategy_prices` blob NOT 开发者_开发技巧NULL,
  `strategy_hedge` blob NOT NULL,
  `strategy_neutrality` int(11) NOT NULL,
  `strategy_valuation_model` int(11) NOT NULL,
  `strategy_source` int(11) NOT NULL,
  PRIMARY KEY (`idStrategy`),
  UNIQUE KEY `strategy_date` (`strategy_date`,`strategy_type`,`strategy_supertype`,`strategy_codes`,`strategy_neutrality`,`strategy_valuation_model`,`strategy_source`),
  KEY `strategy_date_2` (`strategy_date`),
  KEY `strategy_type` (`strategy_type`),
  KEY `strategy_supertype` (`strategy_supertype`),
  KEY `strategy_codes` (`strategy_codes`),
  KEY `strategy_neutrality` (`strategy_neutrality`),
  KEY `strategy_valuation_model` (`strategy_valuation_model`),
  KEY `strategy_source` (`strategy_source`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=888605 ;

I do this query but it is too slow

SELECT * FROM test_ter.superstrat WHERE strategy_date >= '2004-01-01' AND strategy_type='0'
AND strategy_supertype = '0' AND strategy_valuation_model='6'
AND strategy_source ='0' AND strategy_codes='10;' AND strategy_neutrality='2' LIMIT 0,5000;

The reason is it does an index_merge Query on only two indexes : strategy_type,strategy_codes :

1   SIMPLE  superstrat  index_merge strategy_date,strategy_date_2,strategy_type,strategy_supertype,strategy_codes,strategy_neutrality,strategy_valuation_model,strategy_source  strategy_type,strategy_codes    4,42        6258    Using intersect(strategy_type,strategy_codes); Using where

How can I force to index_merge on other fields, here it will extract 6258 rows instead of 1.5k, when my database is full it will take 60 seconds to extract 50000 rows but I'm quite sure it can be reduced to the target 1.5k, I just don't know how. USE INDEX and FORCE INDEX doesn't seem to work.

---

The reason why your query is slow is because you have too many indexes.

When a piece of data is inserted into a mysql table, it's written to a file on the hard drive. As with everything in life and computers, it's easier to look for relevant data in a smaller file than in larger file - hence, indexes. Indexes are written in a separate physical file and the point of index file is that it's smaller than the data file, hence - finding something in the index file is faster and once you find it there, it tells you where in the data file your record is.

When you look at your table now, you'll notice how you indexed every possible column, which is - well, bad. You need to think clearly HOW you'll help the computer help you.

Now, an index is "the best" when it's selectivity is 100%. That means, if you have 100 records and you indexed a column "idStrategy" - you'll have 100 DIFFERENT index values. So, number of different index VALUES divided by number of rows = selectivity (to put it bluntly).

So, the question here is, what column can you use to filter out the data set the most effective? First one that pops to mind is the strategy_date column. It's defined as unique key, it's type is datetime therefore it's saved as 4 byte integer internally which makes it a perfect candidate for BETWEEN type of searches and that's the column that will make the most difference when selecting something from your data set.

Other columns, such as strategy_neutrality and so on can't have many different values so they are poor choice for the index, hence - you don't have to index them.

There's quite a bit to add here, however what I wrote should give you at least SOME insight on how to google for stuff that's unclear. Hope it helps.

---

Well I just switched from MyISAM to InnoDB at it works.

1   SIMPLE  superstrat  index_merge strategy_date,strategy_date_2,strategy_type,strategy_supertype,strategy_codes,strategy_neutrality,strategy_valuation_model,strategy_source  strategy_type,strategy_codes,strategy_source,strategy_supertype,strategy_neutrality,strategy_valuation_model    4,42,4,4,4,4        1248    Using intersect(strategy_type,strategy_codes,strategy_source,strategy_supertype,strategy_neutrality,strategy_valuation_model); Using where