[Freeswitch-dev] sofia DB tables - missing indexes

[kokoska rokoska]

Wasim Baig napsal(a):
> On Sun, Apr 6, 2008 at 9:32 AM, kokoska rokoska <kokoska.rokoska at post.cz 
> <mailto:kokoska.rokoska at post.cz>> wrote:
> 
>         Am I missing something, or should I create indexes by myself? If
>         yes, on
>         what columns?
> 
> 
> that really all depends on what sort of queries you'll be running ... 
> here is a sample schema i use at one place
> 
> CREATE TABLE `cdr` (
>   `accountcode` varchar(20) NOT NULL default '',
>   `caller_id_number` varchar(80) NOT NULL default '',
>   `destination_number` varchar(80) NOT NULL default '',
>   `context` varchar(80) NOT NULL default '',
>   `caller_id` varchar(80) NOT NULL default '',
>   `channel_name` varchar(80) NOT NULL default '',
>   `bridge_channel` varchar(80) NOT NULL default '',
>   `last_app` varchar(80) NOT NULL default '',
>   `last_arg` varchar(80) NOT NULL default '',
>   `start_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
>   `answer_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
>   `end_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
>   `duration` int(11) NOT NULL default '0',
>   `billsec` int(11) NOT NULL default '0',
>   `hangup_cause` varchar(45) NOT NULL default '',
>   `amaflags` varchar(45) NOT NULL default '',
>   `uuid` varchar(36) NOT NULL default '',
>   `userfield` varchar(20) NOT NULL default '',
>   `cost` varchar(20) default NULL,
>   KEY `userfield` (`userfield`),
>   KEY `accountcode` (`accountcode`),
>   KEY `hangup_cause` (`hangup_cause`),
>   KEY `context` (`context`),
>   KEY `destination_number` (`destination_number`),
>   KEY `end_timestamp` (`end_timestamp`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
> 
> -- 
> wasim h. baig | principal consultant | convergence pk | +92 300 8508070
> 
> 

Thank you very much, Wasim, for your answer!

I ask on indexes on tables used by Freeswitch for registrations, 
voicemail etc. The CDR table isn't be much trouble for me I hope, 
because I know exactly what kind of lookups i will do on it :-)
But your SQL query is very good hint for me.

Best regards,

kokoska.rokoska