joseph wrote: > fellow php programmers, > > sorry to you two who were kind enough to take the time to attempt to > answer my question. i thought it was enough to say "i had 550 lines > with a bunch of sql calls and loops and big data structures" psychics-php is a seperate mailing lists, please channel/mindmeld the correct subscription procedure from John Nichel ;-) > > to help you make a better assessment i will summarize in more detail > now. > > for my biggest file ( > > http://www.myowndictionary.com/index.php?old_cfile=rss&cfile=rss&rss_id=198&cid=242&from_lang=korean > > ) i get this: > root@www mysql]# tail -f /var/lib/mysql/mysqld_query.log > out > > [root@www mysql]# cat out | wc -l > 15910 <<< ----------- that's line count how many queries? > [root@www mysql]# head out > 9795 Query select > word,def,wordid,pos,posn,wordsize,syn from korean_english where word > like '운전할 %' order by wordsize desc oh would you look at this.... you're ordering by WORDSIZE. stick an index on WORDSIZE!!! > 9795 Query select > word,def,wordid,pos,posn,wordsize,syn from korean_english where word > like '운전할' order by wordsize desc in cases when you are not using the wildcard tokens (percentage signs) try changing the query to use something like: ... word = '운전할' ... <snip> > > then, it sends each token to CallmatchThis (line 14) which calls > matchThis (line 27 - 47 below) > matchThis may be called twice (2 sql queeries) > (line 51) select * where word = '$token' and another (take that, but if > it's not there .... issue the next sql ) > (line 55) select * where word like '$token%'; Dont do "SELECT *" - always explicitly specify the fields you want. ... > > as for indexes, i only search against word, so i have an index for that. er, you might want to read up a bit on indexes :-) > ------------------------------------------+ > | korean_english | CREATE TABLE `korean_english` ( > `wordid` int(11) NOT NULL auto_increment, > `word` varchar(130) default NULL, > `syn` varchar(190) default NULL, > `def` blob, > `posn` int(2) default '1', > `pos` varchar(13) default '1', > `submitter` varchar(25) default NULL, > `doe` datetime NOT NULL default '0000-00-00 00:00:00', > `wordsize` tinyint(3) unsigned default NULL, > PRIMARY KEY (`wordid`), > KEY `word_idx` (`word`), > KEY `wordid_idx` (`wordid`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | reorder the fields so that the VARCHARS are at the end of the table (and the BLOB field at the very, very end of the table). also change VARCHARs to CHARs where you can. > +----------------+----------------------------------------------------------------------------------------------------------------------------------------- > ----------------------------------------------------------------------------------------------------------------------------------------------------------- > --------------------------------------------------------------------- > > > > > > 1 function MainLoop() you have an iceballs' chance in hell that I'm going to even read the 500+ lines of code that followed here ... let alone try to optimize it. ;-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
