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