I advise you should add a new column to save this information
date_format(cache.server.tstamp,"%Y %M %d %H %i")) and add a new index on
it.
On Sat, Jun 28, 2008 at 8:55 PM, Darryl Steyn <[EMAIL PROTECTED]>
wrote:
> Hi Ananda,
>
> At the moment the explain for the entire month will look the same as the
> one
> I first gave as the database currently only has test data for the start of
> the month.
>
> I will populate the database with more info on Monday, but I have managed
> to
> get it to return only the rows where the minute is 00 or 30 (enough for
> reporting) which does seem to be helping (94rows, 40.27seconds).
>
> Thanks,
> Darryl
>
>
>
> On Sat, Jun 28, 2008 at 8:39 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>
> > The more data u fetch the more time the query takes,.
> > Can u please check the explain plan for the entire month and lets us
> know.
> >
> > If the number of rows processed by db is more than 10% of the total rows,
> > than optimizer considers doing a FULL TABLE scan better than a index scan
> >
> >
> > On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote:
> >
> >> Hi Ananda,
> >>
> >> The problem is the one date is stored as a unix time stamp, and the
> other
> >> as a XXXX-XX-XX XX:XX:XX format :/. Can MySQL just match it withouting?
> >>
> >>
> >> I have dropped the 2nd conversions on the WHERE part of the query as I
> can
> >> format that before executing to look correct.
> >>
> >> It's taking 4.5 seconds to execute when limiting to 10, the logs are
> >> inserted every 5 mins and when you select an entire month you can
> imagine
> >> how long it would take :/
> >>
> >> Thanks for the help so far,
> >> Darryl
> >>
> >> On Fri, Jun 27, 2008 at 6:13 PM, Ananda Kumar <[EMAIL PROTECTED]>
> wrote:
> >>
> >>> Also since mysql give date in Y-M-D you might want to remove the
> >>> "from_unixtime" and "date_format" formating functions
> >>>
> >>> On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote:
> >>>
> >>>> Hi Jörg,
> >>>>
> >>>> I have applied the changes you have suggested and still no joy :(
> >>>>
> >>>> I will carry on playing around with it to see if there is no way else
> >>>> for me
> >>>> to simplify it (maybe selecting only every 30mins or so).
> >>>>
> >>>> Thanks for the help,
> >>>> Darryl
> >>>>
> >>>> On Fri, Jun 27, 2008 at 5:28 PM, Joerg Bruehe <[EMAIL PROTECTED]>
> wrote:
> >>>>
> >>>> > Hi Darryl, all,
> >>>> >
> >>>> >
> >>>> > Darryl Steyn wrote:
> >>>> >
> >>>> >> Hi Ananda,
> >>>> >>
> >>>> >> The query is for reporting purposes and I would like to include a
> >>>> date
> >>>> >> range
> >>>> >> for the user to report on. That part of the query has to be there
> for
> >>>> it
> >>>> >> to
> >>>> >> work nicely.
> >>>> >>
> >>>> >> Regards,
> >>>> >> Darryl
> >>>> >>
> >>>> >> On Fri, Jun 27, 2008 at 4:25 PM, Ananda Kumar <[EMAIL PROTECTED]>
> >>>> wrote:
> >>>> >>
> >>>> >> Hi Darryl,
> >>>> >>> Indexing looks fine, but what are ur trying to achive using this
> >>>> >>> conditions
> >>>> >>>
> >>>> >>> "cache.server.tstamp > 0) AND
> >>>> >>> ((date_format(cache.server.tstamp,'%Y-%m-%d') BETWEEN "2008-05-31"
> >>>> AND
> >>>> >>> "2008-06-10" ))"
> >>>> >>>
> >>>> >>>
> >>>> > IMO, it might help if you could code your condition(s) such that the
> >>>> format
> >>>> > conversion is applied only once (on the constant values),
> >>>> > and not on each row.
> >>>> >
> >>>> > With your current query, each row's "tstamp" value must be converted
> >>>> to
> >>>> > evaluate the condition.
> >>>> > If you would convert the values "2008-05-31" and "2008-06-10" to the
> >>>> format
> >>>> > of your column, you would avoid that (and so reduce load):
> >>>> >
> >>>> > cache.server.tstamp > 0) AND
> >>>> > (cache.server.tstamp BETWEEN conversion ("2008-05-31 00:00:00")
> AND
> >>>> > conversion ("2008-06-10 23:59:59") )
> >>>> >
> >>>> > The correct "conversion" depends on your column's data type.
> >>>> >
> >>>> > Also, your condition "cache.server.tstamp > 0" should not be
> >>>> necessary, as
> >>>> > the BETWEEN will also ensure that.
> >>>> >
> >>>> >
> >>>> > HTH,
> >>>> > Jörg
> >>>> >
> >>>> > --
> >>>> > Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
> >>>> > Sun Microsystems GmbH, Sonnenallee 1, D-85551
> >>>> Kirchheim-Heimstetten
> >>>> > Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland
> >>>> Boemer
> >>>> > Vorsitzender des Aufsichtsrates: Martin Haering Muenchen:
> >>>> HRB161028
> >>>> >
> >>>> >
> >>>>
> >>>
> >>>
> >>
> >
>
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
