Re: what would a c extension buy me

Something that is chiefly bottlenecked at the database isn't going to be improved enough at the C level to be worth the trouble. Always optimize where it's slow. Database design and indexing helps. Minimizing unnecessary queries, writing good ones, helps too. Once you get to the point that your code is the problem, you should look into your algorithms to see if they're as fast as they should be before you dive into C. A crappy algorithm will run like crap in any language. I had a program in C run in three minutes which was, in my estimation, crappy. I re-worked it and used a more sensible algorithm and it now runs in less than a second. Obviously it wasn't the language. In the end maybe an extension will be the way to go for you, depending on your needs, but do everything else you can first. Just my opinion, Ben On Mon, 13 Mar 2006 08:04:53 -0500, Jochem Maas <jochem@iamjochem.com> wrote:

...

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

--