MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow search on one table, many where, order by and limit
Posted by: Yair P
Date: February 26, 2010 11:27AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow search on one table, many where, order by and limit
1974
February 26, 2010 11:27AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.