Need Help of Professional to Speed Up Query

I have an part of code
$nr = mysql_num_rows(mysql_query("Select * from aranan where keyword = '$kelime'"));
if($nr==0) {
@mysql_query("Insert Into aranan (keyword,hit) values ('$kelime','1')");
}else{
@mysql_query("Update aranan set hit = hit+1 where keyword = '$kelime'");

and that part killing server resources, is there any way to optimize it to speed up process ?

 

i'm not sure if this would work for you cause i don't know the language you're using.. but if you can do a try catch statement it could at least rid you of one search..

try {
@mysql_query("Update aranan set hit = hit+1 where keyword = '$kelime'");
}
catch {
@mysql_query("Insert Into aranan (keyword,hit) values ('$kelime','1')");
}

so if $kelime is in the table that is updated, if not it inserts the new row

 

Do you have an index on the keyword column?

If the aranan table is large and you don't have an index, then a table scan would be costly.

 

Language is php

 

I dont have index , and table is over 200,000 , would the index help me ? how much faster it could be with index ?

 

Is there any way to check how fast is the query executed ?

 

Most of my work is with MS SQL Server, not MySQL. That said, the concepts are similar.

In my work, I've seen many cases where the addition of an index leads to a performance increase of 10x, 50x or even 100x.

Furthermore, experience suggests that one should test rather than assume. I suggest that you just add the index to see how it performs.

 

Thank you
help a lot

 

deriklogov - You will notice a remarkable increase in speed once you
create an index in the table arnan on "Keyword".

To see the time taken to execute the query, if you run the query
in phpmyadmin, you can see the time.

 

like everyone is commenting... in mysql indexes make a huge difference as the whole concept is that the position of each value indexed is stored in another table and it can now access it directly... and not have to read row by row until it finds the one you're looking for...

also another bit you might like to use which is more of a way to reduce lines of your code is:

REPLACE INTO test VALUES ('agent2', number+1);

the replace function does exactly what u're trying to do with doing a lookup first and if it doesn't exists inserting else updating...

i think it looks nicer... cooler coding...