Re: [PHP-DB] Table optimization ideas needed

>     That's never going to be fast because you're using innodb tables.
> 
> Should I change it to MyISAM ones?

It depends.

Do you need or use transactions? You can't change - myisam doesn't 
support them.


> No. That's only part of it. I have a cron job, which get the total 
> visits often.

If you are using mysql 5.0+, use a trigger to update a counter:

It'll depend on your data set and what you need to get out of the data 
(ie what queries you are running) but if you just need a total or even 
total per day, this would be the best.

Might need to do it as a stored procedure so you can see if the date 
already exists in that table so you can either do an update or insert, 
or maybe you can use replace into without needing to do that check.

See http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html and 
http://dev.mysql.com/doc/refman/5.0/en/triggers.html - and look at the 
comments:


Triggers can call 'CALL' method.

DROP TRIGGER cinema.TESTTRIGGER;
CREATE TRIGGER cinema.TESTTRIGGER AFTER INSERT ON film FOR EACH ROW
CALL cinema.CHECKFILM('Old boy');

It works. The trigger is perform and do its job. The only request is to 
use the word 'AFTER' when you call your trigger. 'BEFORE' doesn't work 
(the trigger is called ans work but the insert failed in all case).



-- 
Postgresql & php tutorials
http://www.designmagick.com/