... >>>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, note 'In Some Cases'. so stick an index on WORDSIZE and find out. > 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 ........ ah, talk about being caught out :-) regardless a 40line summary won't cut it either - you have to take the block as a whole. > > > > questions i may deem myself to answer: > >>>) 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? > > 15, 910 queries so roughly (15 * 60) seconds to run 15,000+ queries and do the processing? that doesn't actually sound so bad. > > > > 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 never paraphrase code - you only end up with smart ass comments like mine! have you reordered you fields in the db yet? adn made as many VARCHARs as possible into CHARs? > > >> >> >>>------------------------------------------+ >>>| 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
