Yeah,
My tables structure....
+-------+---------------------------+------+-----+---------+----------------
+
| id | int(11) | | PRI | NULL | auto_increment
|
| edate | date | YES | MUL | NULL |
|
| etime | int(5) unsigned zerofill | YES | | NULL |
|
| lec | varchar(5) | YES | | NULL |
|
| trunk | varchar(5) | YES | | NULL |
|
| bound | varchar(5) | YES | | NULL |
|
| att | int(10) unsigned zerofill | YES | | NULL |
|
| suc | int(10) unsigned zerofill | YES | | NULL |
|
| ovf | int(10) unsigned zerofill | YES | | NULL |
|
| fir | int(10) unsigned zerofill | YES | | NULL |
|
| alt | int(10) unsigned zerofill | YES | | NULL |
|
| ter | int(10) unsigned zerofill | YES | | NULL |
|
| rng | int(10) unsigned zerofill | YES | | NULL |
|
| tot | float unsigned zerofill | YES | | NULL |
|
| cal | float unsigned zerofill | YES | | NULL |
|
| mtu | float unsigned zerofill | YES | | NULL |
|
| eqp | int(10) unsigned zerofill | YES | | NULL |
|
| srv | int(10) unsigned zerofill | YES | | NULL |
|
| loc | int(10) unsigned zerofill | YES | | NULL |
|
| flt | int(10) unsigned zerofill | YES | | NULL |
|
+-------+---------------------------+------+-----+---------+----------------
+
20 rows in set (0.00 sec)
I indexed ....
Edate
Etime
Lec
Trunk
Bound
Att
Suc
Cal
The table above is filled with 24 hour data every day...so it grows up to a
million since January...
Before I got the speed....but now..it slows up if you query the maximum
value of every day in one month coverage...so how much more if get the
maximum of every month to cover a one year period...
How do I make a query on this?...I tried the group by...still I stuck on 6
minutes..
R.B.Roa
Traffic Management Engineer
PhilCom Corporation
Tel.No. (088) 858-1028
Mobile No. (0919) 30856267
-----Original Message-----
From: Anvar Hussain K.M. [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 11:38 AM
To: [EMAIL PROTECTED]
Subject: Re: Query Speed
Hi,
Your mail does not tell about the table structure or the index
available.
But try this:
Select truncate(datecol/100,0), max(colyouwantmaxof) from tablename
group by (truncate(datecol/100,0);
Here it is assumed datecol is of date data type. If it is datetime
you will
have to divide by 100000000 instead of 100. A still better solution
is to
add another colum to the table where you can keep the yearmonth of
the date column as an integer and create an index on it.
Anvar.
At 09:40 AM 14/02/2002 +0800, you wrote:
>Hi,
>
>I got stuck on query....about a million rows in one table...it
slows up my
>server...
>
>How can I improve my server on managing a request like this?
>
>I manually loop the monthly data query...I got a database that is
full of
>data daily...so all I need to get the maximum of every month is to
query by
>loop than to use the SELECT MAX() FROM FOO...is there any way to
improve a
>GROUP BY query..i know it can be done in group by...I done it
before...
>
>Anybody done this before....i need your help
>
>R.B.Roa
>Traffic Management Engineer
>PhilCom Corporation
>Tel.No. (088) 858-1028
>Mobile No. (0919) 30856267
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
