I'm running LOCAL WAMPSERVER on a Quad Core 2.5 GHZ with 8 gb of RAM and when I'm running my custom script that read (a lot) in the Mysql DB, it'S pretty slow, a bit slower than when using a shared server.
When I pop the Task Manager, I see the CPU go from 3% to 30% while running the script, but not more.
So my question is why isnt it using at least 90% of it and giving me faster results?
I mean, a computer that run billions of operations/second should process some PHP/mysql script like... a lot faster.
Lighten me up!
(I've search all around and couldnt find the exact same problem...)
EDIT HOLY MACARONI. >> I hope this explanation helps other newbie like me GREATLY speed up their MYSQL database...
THANKS to gregmac & Eduardo Ivanec
I've been programming PHP/Mysql for quite some while now (self thought) and I can create some pretty crazy applications but I never got to create a REALLY big database that needed more than basic optimization knowledge (basically, if you build an application that runs fast, do what its supposed to and is good looking, why learn optimisation?).
But with this application (built for me, to ease my life), I have a 100meg db with "500 000" entries in one table and growing by a lot everyday. Basically, when I first learned MYSQL many years ago, I read quickly how it work and how to query and such but never got around "optimisation" thinking I would solve this when and IF need may be. So basically, I always only used the "INDEX" on the FIRST column of every table thinking that's what it was for.
When I read in
gregmac comment about
"If you are selecting data using a WHERE clause and there is no index on the field[...]" that quickly raised a flag and after some time at http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html, I learned THIS:
Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.
So I went in my db and set the INDEXes on the columns I was querying and BAM, 100x speed improvement! I hope this help other newbies of MYSQL like me!!