How to speed up my database?

[Prismatic]

My Database has 8,553,125 rows in it (an MD5 checksum database) and it's EXTREMELY slow. If i try to run a script which updates the total number of rows in the database to update the main website the activity light on the server turns solid (I run it) and the site just sits there.

 

This is the code I use to get the current rows.. It used to work, before I had 8 million rows

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php

$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT * FROM hash\"[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]$num_rows [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_num_rows[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]);

if([/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"UPDATE seekstats SET hashtotals = \'$num_rows\' WHERE id = \'1\'\"[/span][span style=\"color:#007700\"])or die([/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]())){

    echo [/span][span style=\"color:#DD0000\"]\"Update of Hash Totals successful\"[/span][span style=\"color:#007700\"];

}

else{

    echo [/span][span style=\"color:#DD0000\"]\"There was a problem updating the Hash Totals!\"[/span][span style=\"color:#007700\"];

}

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

The Database has 2 tables, the hash table, and seekstats which just stors the total row value. The hash table has 3 fields, hash, realval, and views. Hash Stores the MD5 hash's, realval stores what they mean, and views is there so in the future I can add a sort of hitcounter to it.

 

None of the fields have a primary key or anything.

 

Export of the hash table:

CREATE TABLE hash (

hash text NOT NULL,

realval text NOT NULL,

views text NOT NULL

) TYPE=MyISAM;

 

Is there a way to speed this up?

 

Edit - Server is a 2.0Ghz Intel P4 system with 512Megs Ram

[Prismatic]

I deleted the database and am in the process of re-inserting the info again. I added an Id field as a primary key auto increment.. Im going to use this to get the total # results

[ryanlwh]

don't use an auto increment id to get total #. if you delete something, you're screwed. use COUNT(*) instead, it's always faster than mysql_num_rows.

[Barand]

The time taken to run a query is proportional to the amount of data returned. You are currently retreiving every column from 8m+ rows just to get a row count.

 

MySQL stores internally the number of rows in a table and will return the total when you

 

$result = mysql_query("SELECT COUNT(*) FROM tablename");
$numrows = mysql_result($result, 0);

 

without having to read the table data.