PHP+MySQL eating too much cpu

[Anon]

Hi, I have a website that is basically a picture gallery site.

Now, users must log in etc, and there are a few other background things that go on while the user is there, relevent to the current user that uses the site.

On average, there will be approximately 30 queries per page load, most of them will have less than 3 results, so they aren't very large.

However, when i query from the actual picture list (one large table with picture name, path, size, dimensions etc) which has over 70,000 rows, each query uses over 2 percent of my CPU, which is a P3-1000.

I use FreeBSD and use 'top' to view the CPU usage. If I reload a page that queries from the picture table I can get it up to over 70 percent and more CPU usage within less than a minute.

I have a Dual P3-1000 box and this is ridiculous cpu usage. I was wondering if anyojne knew of a way for me to make the queries more efficient or simply stop the cpu from being used to heavily.

The table has an index, and none of the queries will call up more than 1000 results. Are there dfferent kinds of indexes? Should I reduce the amount of columns? (find out width and height dynamically, etc)

Thanks in advance!

Decius.

[Anon]

how long does the query take? are joins involved? are you using pconnect?

[Anon]

The query takes perhaps .5 seconds.

There are no joins.

Table Structure:

CREATE TABLE pics (
id mediumint(9) NOT NULL auto_increment,
pictureID varchar(20) NOT NULL,
picturesize smallint(6),
galleryID smallint(6),
picturenumacc int(11) DEFAULT '0' NOT NULL,
picturewidth smallint(6),
pictureheight smallint(6),
picturetype varchar(255),
pictureauthor varchar(255),
pictureauthorlink varchar(255),
PRIMARY KEY (id),
UNIQUE id (id),
UNIQUE cpid (cpid)
);

Query:

$result = mysql_query("select pictureID from pictures where galleryID = '4' order by pictureID", $db);

Each one of these queries takes up 1.5 percent of the cpu on a dual p3-1000 server.

UGH!