|
|
Posted by Steve on 10/11/04 11:39
On Tue, 07 Feb 2006 14:42:57 -0800, 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.
Always normalise your data model first. Then, if it isn't fast enough, add
in shortcuts. Databases ( even mysql (: ) are really powerful - but that
also means that you can really screw everything up if you don't stand back
first, and get a proper idea of what you're trying to do first.
There must be a copy of C.J.Date on the internet somewhere - it must be 30
years old by now.
Steve
Navigation:
[Reply to this message]
|