trying to make a query faster. the list helped out a lot last time, so i
figured i would bring back and get more assistance.
any thoughts?
katen
EXPLAIN SYNTAX:
+---------+-------+-----------------+-----------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+---------+-------+-----------------+-----------+---------+-------+------+-------+
| PROXY | const | PROXY_U,PROXY_I | PROXY_U | 2 | const | 1
| |
| FILTERS | const | FILTERS_U | FILTERS_U | 20 | const | 1
| |
+---------+-------+-----------------+-----------+---------+-------+------+-------+
QUERY:
select PROXY.TARGET_1_AUTH,
PROXY.SHARED_SECRET,
PROXY.AUTH_PORT,
PROXY.ACCT_PORT,
PROXY.RETRIES,
PROXY.TIMEOUT,
PROXY.USEOLDASCENDPASSWORDS,
PROXY.SERVERHASBROKENPORTNUMBERS,
PROXY.SERVERHASBROKENADDRESSES,
PROXY.IGNOREREPLYSIGNATURE,
FILTERS.ALLOW,
FILTERS.ADD,
PROXY.IP_POOL,
PROXY.FAILUREPOLICY
from PROXY, FILTERS
where PROXY.DNIS=substring('9162331155',7,4)
and PROXY.TARGET_1_AUTH is not null
and FILTERS.FILTER=PROXY.FILTER
and PROXY.REALM='' and PROXY.STATUS=1;
CREATE TABLE SYNTAX:
CREATE TABLE `PROXY` (
`ID` smallint(6) NOT NULL auto_increment,
`DNIS` smallint(5) unsigned NOT NULL default '0',
`STATUS` tinyint(4) unsigned NOT NULL default '0',
`REALM` char(40) default NULL,
`IP_POOL` char(20) NOT NULL default '',
`FILTER` char(20) NOT NULL default '',
`TEST_USER` char(30) default NULL,
`TEST_PASS` char(30) default NULL,
`TARGET_1_AUTH` char(25) NOT NULL default '',
`TARGET_1_ACCT` char(25) default NULL,
`TARGET_2_AUTH` char(25) default NULL,
`TARGET_2_ACCT` char(25) default NULL,
`TARGET_3_AUTH` char(25) default NULL,
`TARGET_3_ACCT` char(25) default NULL,
`TARGET_4_AUTH` char(25) default NULL,
`TARGET_4_ACCT` char(25) default NULL,
`AUTH_PORT` smallint(6) default NULL,
`ACCT_PORT` smallint(6) default NULL,
`SHARED_SECRET` char(25) NOT NULL default '',
`RETRIES` tinyint(4) unsigned NOT NULL default '3',
`TIMEOUT` tinyint(4) unsigned NOT NULL default '20',
`USEOLDASCENDPASSWORDS` int(11) default NULL,
`SERVERHASBROKENPORTNUMBERS` int(11) default NULL,
`SERVERHASBROKENADDRESSES` int(11) default NULL,
`IGNOREREPLYSIGNATURE` int(11) default NULL,
`REPLYHOOK` char(255) default NULL,
`FAILUREPOLICY` tinyint(4) default '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `PROXY_U` (`DNIS`),
KEY `PROXY_I` (`REALM`,`TARGET_1_AUTH`,`STATUS`,`FILTER`)
)
CREATE TABLE `FILTERS` (
`ID` tinyint(4) NOT NULL auto_increment,
`FILTER` varchar(20) NOT NULL default '',
`ALLOW` text,
`ADD` text,
PRIMARY KEY (`ID`),
UNIQUE KEY `FILTERS_U` (`FILTER`)
)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
