Re: Slow search on one table, many where, order by and limit

[Yair P]

Rick James Wrote:
-------------------------------------------------------
> See how well it works with:
> INDEX(a,j),
> INDEX(b,j),
> INDEX(c,j),
> INDEX(d,j),
> INDEX(e,j)
>
> It's not 32, but rather 5 factorial = 120


Rick, Thanks man!!!

That's a wonderful idea and works like a charm. Now all queries takes just a small fractin of a second.

I have another optimization problem. When using ORDER BY RAND() it performs poorly.

For example:

SELECT CARD_ID FROM cards		// with order by LAST_VISITED_DATE
	WHERE (LOOKING_FOR_GENDERS +0 &2) >0 AND 
	GENDER IN ('MAN') AND 
	COUNTRY_ID =254 AND 
	(CARD_FLAGS +0 &18) >0
	ORDER BY LAST_VISITED_DATE DESC 
	LIMIT 0 , 320 	
time: 0.01 sec

+----+-------------+-------+------+-------------------+--------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys     | key    | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+-------------------+--------+---------+-------+--------+-------------+
|  1 | SIMPLE      | cards | ref  | COUNTRY_ID,GENDER | GENDER | 2       | const | 189714 | Using where |
+----+-------------+-------+------+-------------------+--------+---------+-------+--------+-------------+

	

but when I use order by rand():

SELECT CARD_ID FROM cards		// with order by rand()
	WHERE (LOOKING_FOR_GENDERS +0 &2) >0 AND 
	GENDER IN ('MAN') AND 
	COUNTRY_ID =254 AND 
	(CARD_FLAGS +0 &18) >0
	ORDER BY RAND( ) 
	LIMIT 0 , 320 	
time: 3.5 sec !!
+----+-------------+-------+------+-------------------+--------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key    | key_len | ref   | rows   | Extra                                        |
+----+-------------+-------+------+-------------------+--------+---------+-------+--------+----------------------------------------------+
|  1 | SIMPLE      | cards | ref  | COUNTRY_ID,GENDER | GENDER | 2       | const | 189714 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+--------+---------+-------+--------+----------------------------------------------+
	

Here are the keys I'm using:
  PRIMARY KEY  (`CARD_ID`),
  KEY `USERNAME` (`USERNAME`,`PASSWORD`),
  KEY `EMAIL` (`EMAIL`),
  KEY `CARD_FLAGS` (`CARD_FLAGS`),
  KEY `LAST_VISITED_DATE` (`LAST_VISITED_DATE`),
  KEY `COUNTRY_ID` (`COUNTRY_ID`,`STATE_ID`,`CITY_ID`),
  KEY `LOOKING_FOR_GENDERS` (`LOOKING_FOR_GENDERS`,`LAST_VISITED_DATE`),
  KEY `GENDER` (`GENDER`,`LAST_VISITED_DATE`),
  KEY `CARD_FLAGS_DATE` (`CARD_FLAGS`,`LAST_VISITED_DATE`),
  KEY `NARITAL_DATE` (`MARITAL`,`LAST_VISITED_DATE`),
  KEY `ETHNICITY_DATE` (`ETHNICITY`,`LAST_VISITED_DATE`),
  KEY `LANGUAGES_DATE` (`LANGUAGES`,`LAST_VISITED_DATE`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=428043 ;

Any ideas how to handle the order by rand() ?

Thanks