ключ MySQL key

подскажите как сейчас правильно? я внизу написал индексы

]photo_files | CREATE TABLE `photo_files` (
  `id_pf` int(7) NOT NULL auto_increment,
  `id_pa` int(7) NOT NULL,
  `id_un` int(7) NOT NULL,
  `name_pf` varchar(50) default NULL,
  `description_pf` varchar(600) default NULL,
  `created` int(11) NOT NULL,
  `hidden_pf` tinyint(1) NOT NULL default '0',
  `viewing` int(11) NOT NULL default '0',
  `size_pf` mediumint(9) default NULL,
  `x_pf` mediumint(9) default NULL,
  `y_pf` mediumint(9) default NULL,
  PRIMARY KEY  (`id_pf`)
) ENGINE=MyISAM AUTO_INCREMENT=161 DEFAULT CHARSET=utf8 | 

PRIMARY KEY (`id_pf`, id_pa, id_un )

INDEX viewing (viewing)

INDEX size_pf (size_pf)

INDEX created (created) - для сортировки

 photo_albums | CREATE TABLE `photo_albums` (
  `id_pa` int(7) NOT NULL auto_increment,
  `id_co` int(7) default NULL,
  `id_un` int(7) NOT NULL,
  `name_pa` varchar(50) NOT NULL,
  `description_pa` varchar(600) default NULL,
  `created` int(11) NOT NULL,
  `hidden_pa` tinyint(1) NOT NULL default '0',
  `privat_pa` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id_pa`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 |

PRIMARY KEY ( id_pa, id_un )

INDEX viewing (viewing)

INDEX size_pf (size_pf)

INDEX created (created) - для сортировки

INDEX hidden_pa (hidden_pa)

INDEX privat_pa (privat_pa)

== id_co не поставил, так как оно разное может быть ==

 comment | CREATE TABLE `comment` (
  `id_cm` int(11) NOT NULL auto_increment,
  `id_co` int(11) NOT NULL,
  `id_se` int(11) NOT NULL,
  `id_un` int(11) NOT NULL,
  `name_guest` varchar(255) default NULL,
  `text_cm` text NOT NULL,
  `hiden_g_cm` tinyint(1) NOT NULL default '1',
  `created` int(11) NOT NULL,
  `host` varchar(255) default NULL,
  `forwarded` varchar(20) default NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY  (`id_cm`)
) ENGINE=MyISAM AUTO_INCREMENT=175 DEFAULT CHARSET=utf8 COMMENT='X0 - comment' | 

PRIMARY KEY ( id_cm, id_co, id_se, id_un )

INDEX hiden_g_cm (hiden_g_cm)

INDEX created (created) - для сортировки

поиск по этом полям нужно будет делать:

UNIQUE host (host) - может быть NULL

UNIQUE forwarded (forwarded) - может быть NULL

UNIQUE ip (ip) - может быть NULL

 video_files_preview | CREATE TABLE `video_files_preview` (
  `id_vfp` mediumint(9) NOT NULL auto_increment,
  `id_vf` mediumint(9) NOT NULL,
  `id_va` mediumint(9) NOT NULL,
  `id_un` mediumint(9) NOT NULL,
  PRIMARY KEY  (`id_vfp`)
) ENGINE=MyISAM AUTO_INCREMENT=197 DEFAULT CHARSET=utf8 | 

PRIMARY KEY (`id_vfp`, id_vf, id_va, id_un )

====================

====================

еще нашел:

чтобы сделать по двум полял полнотексный поиск FULLTEXT (title,body)

но ТОЛЬКО с title индекс работать не будет, нужно сразу с оба делать

=====

=====

откректировал пост, вообщем не знаю как правильней

там где поиск, и выборка по ним, там надо поставить INDEX ?

ограничения уникальности я потавил в PRIMARY KEY чтобы там несколько связать

===

в примерах нигде не написано, как - не знаю,

может там наверное надо было, так написать KEY unik (id_pa, id_pf, id_un) вместо PRIMARY KEY ???

rtyug добавил 18.12.2009 в 01:05

ну вот например один запрос, как к нему индесы поставить?

SELECT t1.id_co,  
                                 t1.id_va,            
                                       t1.id_un,             
                                           t1.name_va,          
                                              t1.description_va,  
                                              t1.hidden_va,          
                                              t1.created,    
                                              t1.privat_va,        
                                                         
                                              t2.name_co,
                                    t2.active_co,
                                              t2.hiden_co,         
                                              t2.hiden_g_co,
                                              t2.close_co,
                                              t3.hiden_g_co AS hiden_g_se,    
                                              t3.active_se,                                
                                              t4.username,

                                              (SELECT count(*) 
                                                 FROM video_files AS t5 
                                                WHERE t1.id_va = t5.id_va
                                                      ) AS count_video_files,

                                              (SELECT SUM(t6.size_vf) 
                                                 FROM video_files AS t6 
                                                WHERE t1.id_va = t6.id_va
                                                      ) AS sum_v_video_files,
                                                      
                                              (SELECT min(t7.id_vf) 
                                                 FROM video_files AS t7,
                                                       video_files_preview AS t8                                                   
                                                WHERE t1.id_va = t7.id_va
                                                     AND t7.size_vf is NOT NULL 
                                                 AND t7.size_vf > 0  
                                                 AND t7.id_vf = t8.id_vf                                                          
                                                      ) AS video_files ,
                                                      
                                              (
                                            SELECT MIN(t10.id_vfp)                 
                                                    FROM video_files_preview AS t10
                                               WHERE t10.id_vf = video_files
                                                   AND t10.id_vf = video_files
                                                LIMIT 1                                              
                                              )    AS min_v_video_files                                                    
                                                                                      
                                                                                                      
                            FROM video_albums AS t1        

                       LEFT JOIN users AS t4
                              ON t1.id_un = t4.id           
                            
                              AND t1.active_co = 1
                     AND t1.hiden_co = 0
                     AND t1.hiden_g_co = 0'
                               
                           ORDER BY t1.created desc   
                           LIMIT ?,?

| video_files | CREATE TABLE `video_files` (
  `id_vf` mediumint(9) NOT NULL auto_increment,
  `id_va` mediumint(9) NOT NULL,
  `id_un` mediumint(9) NOT NULL,
  `name_vf` varchar(50) default NULL,
  `description_vf` varchar(600) default NULL,
  `created` int(11) NOT NULL,
  `hidden_vf` tinyint(1) NOT NULL default '0',
  `viewing` int(11) NOT NULL default '0',
  `size_vf` mediumint(9) default NULL,
  `duration` varchar(15) default NULL,
  `convert_vf` tinyint(1) default '0',
  PRIMARY KEY  (`id_vf`)


| video_albums | CREATE TABLE `video_albums` (
  `id_va` mediumint(9) NOT NULL auto_increment,
  `id_co` mediumint(9) default NULL,
  `id_un` mediumint(9) NOT NULL,
  `name_va` varchar(50) NOT NULL,
  `description_va` varchar(600) default NULL,
  `created` int(11) NOT NULL,
  `hidden_va` tinyint(1) NOT NULL default '0',
  `privat_va` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id_va`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | 

| video_files_preview | CREATE TABLE `video_files_preview` (
  `id_vfp` mediumint(9) NOT NULL auto_increment,
  `id_vf` mediumint(9) NOT NULL,
  `id_va` mediumint(9) NOT NULL,
  `id_un` mediumint(9) NOT NULL,
  PRIMARY KEY  (`id_vfp`)
) ENGINE=MyISAM AUTO_INCREMENT=197 DEFAULT CHARSET=utf8 | 


| content | CREATE TABLE `content` (
  `id_co` int(11) NOT NULL auto_increment,
  `id_un` int(11) NOT NULL,
  `id_se` int(11) NOT NULL,
  `name_co` varchar(255) NOT NULL,
  `heading_name_co` varchar(800) NOT NULL,
  `keys_co` varchar(800) NOT NULL,
  `text_co` text NOT NULL,
  `active_co` tinyint(1) NOT NULL default '1',
  `hiden_co` tinyint(1) NOT NULL default '1',
  `hiden_g_co` tinyint(1) NOT NULL default '0',
  `close_co` tinyint(1) NOT NULL default '1',
  `voting_co` tinyint(1) NOT NULL default '1',
  `vo_all_co` int(11) NOT NULL default '1',
  `vo_balls_co` int(11) NOT NULL default '1',
  `vo_per` varchar(20) NOT NULL default '0',
  `created` int(11) NOT NULL,
  `modified` int(11) NOT NULL default '1',
  `forbi_comm_co` tinyint(1) NOT NULL default '0',
  `viewing` int(11) default NULL,
  `ip` varchar(20) NOT NULL,
  `host` varchar(255) default NULL,
  `forwarded` varchar(20) default NULL,
  PRIMARY KEY  (`id_co`)
) ENGINE=MyISAM AUTO_INCREMENT=76 DEFAULT CHARSET=utf8 COMMENT='X0 - content category' |