In the last episode (Mar 14), abdelhamid bettache said:
> "In some circumstances it can be beneficial to split into two a
> table that is scanned very often" I found these sentence in the mysql
> manual , section 12.3:Get your Data as small as possible
>
> Does every body know what are these circumstances?
One case that comes to mind is when you have a table that you both need to
write to often and read from often. If the columns you are reading from
and writing two are different, then you can break them out into different
tables.
This is in-line with a general rule of thumb that a table should be mostly
read from or mostly written to, not both equally (as databases have trouble
doing both efficiently).
Here's an example of a case I had a while back. I had an "Accounts" table
which contained username, password, and last-access columns. The
username/password fields were read from often, and the last-access column
was written to often. So, I broke out the last-access column into its own
table. This reduced the load on the Accounts table and thus speeded up
access. With "mysqladmin processes" I had been seeing clients waiting for
access to the Accounts table while the Last updates were happening.
Meanwhile, I actually stopped using UPDATE to change the last-access column
in the new Lasts table, instead I now use INSERT DELAYED. I did this
because it's my understanding that UPDATEs are slower than INSERTs, plus
using DELAYED has its performance benefits as well.
This did complicate reading the last-access info, I have to do an "SELECT
last-access FROM Lasts ORDER BY last-access DESC LIMIT 1" to get the most
recent info. Plus I have a nightly script that cleans-up(deletes)
non-latest Lasts rows. But, all this did solve my db performance problem.
-bill
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
