MySQL Problems

The site is slow lately, and I am doing everything to get it back on track... One of the main problems is the way NEXT en PREV images are calculated.. The query that is creating those long locks on the tables is :

SELECT id,inputdate FROM picture WHERE id < $id ORDER BY id DESC LIMIT 1;
SELECT id,inputdate FROM picture WHERE id > $id ORDER BY id ASC LIMIT 1;

These queries lock this table, and costs a lot of CPU power, if somebody has suggestions to make a quicker query to select the next or prev id, I am thinking of removing the NEXT and PREV for normal members,.. and introducing it to Sponsor members only...

One friend of mine is a real wizard with php and sql, I could ask if he has any toughts on how to make this thing smoother.

We need to know a little more about your table structure. If your id field is set to primary and auto-incrementing and your id's are in numerical order and never missing an id you could simplify it by using.

SELECT id,inputdate FROM picture WHERE id = $id - 1;
SELECT id,inputdate FROM picture WHERE id = $id + 1;

There are several other options, such as using the DISTINCT tag. Post some more info and I will help you out.

robt: the id field is "id bigint(20) DEFAULT '0' NOT NULL AUTO_INCREMENT" problem is if some pictures are deleted some id's are missing, and next/prev's isn't working anymore.

The queries I made are slow queries, and take a lot of performance, because he has to order the whole 53000 rows... You should think SQL had some nice commands to get on row higher and one row lower then the current row.... But it's a hell, the slow performance of wbw is because of this query.. tables are locked for 6 seconds... and this queries takes sometimes up to 10 / 12 seconds..

just disabel it until you find a better way, or just not at all :P

I have disabled the next/prev function for now.. I am trying to fix it.. if I have an solution i re-activate it!