Gerard Daubar wrote:
> I had one large table with information on the article and the article
> itself in the table. Now I split the table into the Info and the actual
> Article...
>
> This is nice because the actual articles are LONGTEXT type and could
> slow queries down I would imagine....and this would be unnecesary
> because at times I only need the Info part. For the queries that do need
> the article Info and actual Article...will the speed of having the 2
> queries (1 in each table) be any slower then if I had them still as one
> table?
>
> Thanks,
>
> Gerard
>
Gerard:
I use the same type of structure to store thousands of articles on our web
site. I divide the table in two: a fast lookup table with fixed-length
records storing information about stories, and another table with variable
length and BLOB fields that stores the actual stories. I find this very
fast, since all the lookups are done on the fixed-length table. We serve
tens of millions of dynamic pages every month, using Apache and mod_perl.
MySQL is very fast at doing a join on the primary key (story_id).
Database: story Table: story Rows: 177232
+-------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------+------------------+------+-----+---------------------+----------------+
| story_id | int(11) | | PRI | 0 |
auto_increment |
| story_date | date | | MUL | 0000-00-00
| |
| market | char(20) | | MUL |
| |
| filename | char(25) | | MUL |
| |
| edition | char(15) | | |
| |
| c_time | datetime | | | 0000-00-00 00:00:00
| |
| m_time | timestamp(14) | YES | |
| |
| pub_date | date | | MUL | 0000-00-00
| |
| story_time | time | | | 00:00:00
| |
+-------------+------------------+------+-----+---------------------+----------------+
Database: story Table: story_data Rows: 177232
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| story_id | int(11) | | PRI | 0 | |
| column_name | varchar(100) | YES | | | |
| byline | varchar(100) | YES | | | |
| bylineinfo | varchar(100) | YES | | | |
| copyright | varchar(100) | YES | | | |
| original_byline | varchar(255) | YES | | | |
| headline | varchar(255) | YES | | | |
| subhead | varchar(255) | YES | | | |
| executive_summary | text | YES | | | |
| paragraph1 | text | YES | | | |
| paragraph2 | text | YES | | | |
| story | mediumtext | YES | | | |
+-------------------+--------------+------+-----+---------+-------+
We also do three and four table joins, for stories in categories and
subcategories, all very fast. We also do full-text searching
(http://www.amcity.com/search.html) based on MySQL.
# mysqladmin status
Uptime: 6222725 Threads: 154 Questions: 43444587 Slow queries: 533
Opens: 8700 Flush tables: 1 Open tables: 127
# w
7:05pm up 94 days, 3:44, 2 users, load average: 0.08, 0.06, 0.00
As you can see, we've been averaging about 7 queries per second for the past
72 days or so, with a peak of about 250 queries per second. The box (dual
180MHz r10000 SGI Origin 200, 512 MB RAM) barely notices the load.
Dan
--
Daniel Koch
American City Business Journals
http://www.amcity.com/
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
