Re: what would a c extension buy me

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



...

>>>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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux