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 02:02PM

Hi Rick and thanks again.

I've selected this structure specifically to help the search. Mysql has to use only the simplest binary operations on a single table, rather than six or seven JOINs with other tables.
And indeed sfter using your indexes regular searches takes ~ 100ms

To your questions:

COUNTRY_ID =254 - This is where most of the cards are (about 330k)

CARD_ID may have gaps when a member removes his/her card

There are three genders (man,woman,couple) so the combinations are 18 if I'm not mistaken

LANGUAGES is a set and I'm using binary operations



Here are the queries:
mysql> SHOW CREATE TABLE cards\G
*************************** 1. row ***************************
       Table: cards
Create Table: CREATE TABLE `cards` (
  `CARD_ID` int(10) unsigned NOT NULL auto_increment,
  `USERNAME` varchar(20) NOT NULL default '',
  `PASSWORD` varchar(20) NOT NULL default '',
  `EMAIL` varchar(100) NOT NULL default '',
  `NICK_NAME` varchar(50) NOT NULL default '',
  `REGISTRATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `LAST_VISITED_DATE` datetime default NULL,
  `SUBSCRIPTION_EXPIRATION` datetime default NULL,
  `BIRTHDATE_DAY` smallint(4) NOT NULL default '0',
  `BIRTHDATE_MONTH` smallint(4) NOT NULL default '0',
  `BIRTHDATE_YEAR` smallint(4) NOT NULL default '0',
  `LANGUAGE_CODE` smallint(4) unsigned default '0',
  `COUNTRY_ID` int(10) unsigned NOT NULL default '0',
  `STATE_ID` int(10) unsigned default '0',
  `CITY_ID` int(10) unsigned default '0',
  `SUBCITY_ID` int(10) unsigned default '0',
  `GENDER` enum('MAN','WOMAN','COUPLE') default NULL,
  `LOOKING_FOR_GENDERS` set('MAN','WOMAN','COUPLE') default NULL,
  `RELATIONSHIPS` set('MARRIAGE','LONG_TERM','LOVE','SEX','FRIENDSHIP','ONE_NIGHT_STAND','CHAT','SWINGING','DOMINATION','SUPPORT') default NULL,
  `FUN_TYPES` set('MUSIC','DINING','MOVIES','TV','PUB','INTERNET','TRAVEL','SHOPPING','SPORT','OTHER') default NULL,
  `LANGUAGES` set('ENGLISH','SPANISH','RUSSIAN','FRENCH','CHINESE','OTHER_LANGUAGE') default NULL,
  `HEIGHT` enum('155cm','157cm','160cm','163cm','165cm','168cm','170cm','173cm','175cm','178cm','180cm','183cm','185cm','188cm','191cm','193cm','196cm','198cm','201cm','203cm','206cm','208cm','211cm') default NULL,
  `BODY_STYLE` enum('FIT','ATHLETIC','THIN','AVERAGE','FEW_EXTRA','FULL') default NULL,
  `EYE_COLOR` enum('BROWN','DARK_BROWN','HONEY','BLACK','BLUE','GREEN','BROWN_GREEN','OTHER_EYE') default NULL,
  `HAIR_COLOR` enum('BLACK','BROWN','BLONDE','RED','GRAY','HAIRLESS','OTHER_HAIR') default NULL,
  `HAIR_LENGTH` enum('VERY_SHORT','SHORT','SHOULDER_LENGTH','LONG','BALDING','BALD') default NULL,
  `MARITAL` enum('SINGLE','DIVORCED','SEPARATED','MARRIED','OTHER_MARITAL') default NULL,
  `WEALTH` enum('NOT_SAY','WEALTHY','UPPER_MIDDLE','AVERAGE','LOWER_MIDDLE','NO_MEANS') default NULL,
  `SMOKING` enum('NOT_SMOKING','OCCASIONAL_SMOKER','SMOKING','HEAVY_SMOKER','QUITTING') default NULL,
  `DRINKING` enum('DO_NOT_DRINK','SPECIAL_OCCASIONS','DRINK_SOMETIMES','DRINKING_OFTEN') default NULL,
  `ETHNICITY` enum('AFRICAN_AMERICAN','ASIAN','CAUCASIAN','EAST_INDIAN','HISPANIC','MIDDLE_EAST','NATIVE_AMERICAN','OTHER_ETHNICITY') default NULL,
  `RELIGION` enum('CHRISTIAN','HINDU','JEWISH','MUSLIM','OTHER_RELIGION') default NULL,
  `SEX_ORIENTATION` enum('STRAIGHT','GAY','BISEXUAL','BICURIOUS') default NULL,
  `PARTNER_QUALITIES` set('OPEN_MIND','FUNNY','AGGRESSIVE','THOUGHTFUL','EXPERIENCED','VIRGIN','ATTRACTIVE','CREATIVE','HYGIENIC','GENEROUS','IMAGINATIVE','DISCREET','SHY','HORNY') default NULL,
  `PARTNER_FUN_TYPES` set('MUSIC','DINING','MOVIES','TV','PUB','INTERNET','TRAVEL','SHOPPING','SPORT','OTHER') default NULL,
  `SEX_TYPES` set('MASSAGE','BATH','WATCHING','MUSIC','PORN','FOREPLAY','TOYS','ORAL','LEATHER','SADO_MAZO','PLACES','KING_QUEEN') default NULL,
  `SEX_POSITIONS` set('MAN_ON_TOP','WOMAN_ON_TOP','DOGGY','SIXTY_NINE','SELF','STANDING') default NULL,
  `MAIN_IMAGE_NUM` smallint(4) default NULL,
  `TIMEZONE` int(11) default NULL,
  `CARD_FLAGS` set('EMAIL_VEIRIFIED','HAS_IMAGES','REBILL','CHANGED_TEXT','BEST') default NULL,
  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 AUTO_INCREMENT=428043 DEFAULT CHARSET=utf8



mysql> SHOW TABLE STATUS LIKE 'cards'\G
*************************** 1. row ***************************
           Name: cards
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 328042
 Avg_row_length: 124
    Data_length: 40744584
Max_data_length: 281474976710655
   Index_length: 69526528
      Data_free: 0
 Auto_increment: 428043
    Create_time: 2010-02-26 19:04:11
    Update_time: 2010-02-26 19:05:24
     Check_time: 2010-02-26 19:05:24
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:



mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+----------+
| Variable_name                 | Value    |
+-------------------------------+----------+
| bulk_insert_buffer_size       | 8388608  |
| innodb_buffer_pool_awe_mem_mb | 0        |
| innodb_buffer_pool_size       | 49283072 |
| innodb_log_buffer_size        | 1048576  |
| join_buffer_size              | 131072   |
| key_buffer_size               | 26214400 |
| myisam_sort_buffer_size       | 35651584 |
| net_buffer_length             | 16384    |
| preload_buffer_size           | 32768    |
| read_buffer_size              | 65536    |
| read_rnd_buffer_size          | 262144   |
| sort_buffer_size              | 262144   |
+-------------------------------+----------+

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow search on one table, many where, order by and limit
2097
February 26, 2010 02:02PM


Sorry, you can't reply to this topic. It has been closed.

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.