Re: [PHP-DB] Table optimization ideas needed

On Thu, Mar 27, 2008 at 10:40 AM, Chris <[email protected]> wrote:

>
>     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.
>

I haven't tried transactions so far. :(

>
>
>
>  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 is 5.0+.


>
> 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.
>

Good idea. But I wonder whether calling the trigger each insert will loose
any performance.


>
> 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/
>



-- 
Regards,
Shelley