many points given to my esteemed and humble correspondant for: 1) > psychics-php is a seperate mailing lists, please channel/mindmeld the correct > subscription procedure from John Nichel ;-)
> points to me for a quick comeback ( word is indexed, wordsize is not) 1) per mysql website ... > > > > > 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 :-) > > [EMAIL PROTECTED] 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!!! http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: The key used to fetch the rows is not the same as the one used in the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1; 2) > 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. ;-) > it was just a 40 line summary of 550 lines of code ........ questions i may deem myself to answer: > > > > ) i get this: > > [EMAIL PROTECTED] mysql]# tail -f /var/lib/mysql/mysqld_query.log > out > > > > [EMAIL PROTECTED] mysql]# cat out | wc -l > > 15910 <<< ----------- that's line count > > how many queries? 15, 910 queries > things already taken care of: 1) > > 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 = '운전할' ... your suggestion and a line from the query_log match exactly. 2) > <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. > that was just a paraphrase. the previous email points to the line number of the code summary > > > > > ------------------------------------------+ > > | 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() -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
