Jump to content


Photo

How to speed up my database?


  • Please log in to reply
3 replies to this topic

#1 Prismatic

Prismatic
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego

Posted 28 October 2005 - 05:55 AM

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

#2 Prismatic

Prismatic
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego

Posted 28 October 2005 - 07:07 AM

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

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 28 October 2005 - 04:08 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2005 - 05:43 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users