|
|
Posted by Jerry Stuckle on 10/15/32 11:39
frizzle wrote:
> Jerry Stuckle wrote:
>
>>frizzle wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>frizzle wrote:
>>>>
>>>>
>>>>>Jerry Stuckle wrote:
>>>>>
>>>>>
>>>>>
>>>>>>frizzle wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi there,
>>>>>>>
>>>>>>>I have a mySQL system with a news publishing part in it:
>>>>>>>Admins can create new items with text in it, and they have an option to
>>>>>>>create 'fulltexts', so you'd get "read more ..." on the front page,
>>>>>>>click it and read the fulltext.
>>>>>>>
>>>>>>>Is there a possibility for mySQL (query) to check if 'fulltext' is
>>>>>>>empty or not, and only return true or false, so i don't have to put the
>>>>>>>whole fulltext into the mysql_fetch_array() to decide wether or not to
>>>>>>>show 'read_more', or should i create an extra boolean field in the DB
>>>>>>>saying fulltext y/n ?
>>>>>>>
>>>>>>>Greetings Frizzle.
>>>>>>>
>>>>>>
>>>>>>Put it in a separate table with just the article's id and the text. If
>>>>>>the id exists in the second table, there is more text.
>>>>>>
>>>>>>--
>>>>>>==================
>>>>>>Remove the "x" from my email address
>>>>>>Jerry Stuckle
>>>>>>JDS Computer Training Corp.
>>>>>>jstucklex@attglobal.net
>>>>>>==================
>>>>>
>>>>>
>>>>>This would have me quering against an extra table. Would'nt it be
>>>>>quicker to add an extra boolean field then?
>>>>>
>>>>>Frizzle.
>>>>>
>>>>
>>>>Why? Joins are quick. Probably a lot faster than unnecessarily
>>>>processing empty text fields in the first table.
>>>>
>>>>--
>>>>==================
>>>>Remove the "x" from my email address
>>>>Jerry Stuckle
>>>>JDS Computer Training Corp.
>>>>jstucklex@attglobal.net
>>>>==================
>>>
>>>
>>>I understand what you're saying. I think i'm underestimating the speed
>>>of Joins (mySQL)
>>>Not to be a nag, but would it still be faster to add an extra field to
>>>the original table, 'fulltext_available' tinyint(1), which tells me to
>>>look for it or not?
>>>
>>>And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
>>>in the query, it leaves the unmentioned fields unbothered, thus not
>>>being influenced by their size/contents ?
>>>
>>>Frizzle.
>>>
>>
>>Yes and no. It depends on the implementation of the database.
>>
>>In MySQL, everything in a table is kept in one file. So if you have
>>your full text in that table, a table scan will read everything in the
>>table and throw away the columns you don't want. If you have a lot of
>>text in relation to the rest of the row data, this can be significant
>>overhead. If the full text is in a separate table, it will only be read
>>when you request information from that table.
>>
>>Additionally, is the absolute speed necessary? Are you talking dozens
>>(or even hundreds) of queries per second? I doubt it.
>>
>>Keeping large amounts of data you only occasionally access is generally
>>a better way of doing things. Putting a T/F column in the table just
>>makes for another column you have to manage (and violates normalization
>>principles).
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>jstucklex@attglobal.net
>>==================
>
>
> I don't know (yet) how much queries there will be, but i guess not
> *that* much.
> My lack of english causes me te seize your intention in the last part.
> Do you mean i should put the fulltexts in another extra table?
> I btw hardly doubt if it will be neccesary, since those texts won't be
> that big either.
> (For mySQL / PHP then ... )
> Again, it's a news system, there won't be a whole version of The Da
> Vinci Code behind 'read more'.
>
> Frizzle.
>
Yes, I would put the fulltext in a separate table. The join takes very
little overhead (since primary keys have an index associated).
Very often if you have large amounts of text (several hundred bytes
minimum) which you don't always need to access you can save processing
time by putting it in its own table.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|