Re: Table optimization ideas needed

On Thu, Mar 27, 2008 at 10:40 AM, Chris <dmagick@gmail.com> 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