[mythtv] Re: PATCH: mythfilldatabase v1.43 -- arbitrary end times

[Shawn Pearce]

Usually a database won't index a column that holds null values.  Err,
well, it'll index it, but only for values that are NOT NULL.  NULL
values in the column will cause the row to be skipped in the index,
preventing the index from being used in IS NULL queries.

I don't know if endtime is declared to hold NULLs, but just thought
I'd drop that comment.  :)


Andy Davidoff <dert at pobox.com> wrote:
> What MySQL is optimized for is indexing large tables so that queries
> that retrieve a few specific records based on indexed values are fast.
> 
> If you want to search for programs with no endtimes, create an index
> on the endtime column:
> 
> 	alter table program add index (endtime);
> 
> If you want to check just how many records MySQL is searching in order
> to honor your query:
> 
> 	explain select chanid from program where not endtime;
> 
> I don't have access to my Myth box at the moment, but you get the idea.
> 
> Indexing the database, which eats disk space and slows down inserts and
> deletions, does not seem to be an issue to the Myth developers.
> 
> If you want further help on optimizing this, email me offlist...
> 
> 
> #if J /* Mar 03, 04:45 */
> > Yes, however when I was experimenting with the algorithm,  I found that the 
> > batch file to create the temp databases,  was both faster and less memory 
> > intensive.  Since this approach needs to have the entire program database 
> > indexed,  doing that without the temp databases would mean that the whole 
> > program database would have to be read into memory, indexed, and then, 
> > rather than doing comparisons on the very small subset of entries (in 
> > temp2), you would end up reviewing all the entries. For example, my program 
> > database has 17603 records, but temp2 only contains 465.  Since mysql is 
> > already optimized for this sort of operation, it seemed the most efficient 
> > and elegant solution.
> > 
> > That being said,  is it the use of the temp tables, the batch file, or both 
> > that you find objectionable?
> #endif /* JTech at interbaun.com */
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at snowman.net
> http://www.snowman.net/mailman/listinfo/mythtv-dev

-- 
Shawn.

  Q:	What does a WASP Mom make for dinner?
  A:	A crisp salad, a hearty soup, a lovely entree, followed by
  	a delicious dessert.