List:General Discussion« Previous MessageNext Message »
From:christopher.l.hood Date:July 28 2004 7:10pm
Subject:RE: Indexed Database still slow
View as plain text  
YAHOOO !!!! Shawn Green you were absolutely right, I basically just took
your 6 union query from below, modified it to add back my specific data
and fixed my index to only include the 2 fields that I needed and VOILA`
it worked like a champ.

My query times went from 1:05 to 0.32 seconds, thanks for all your help.


Thanks to every one in the community that helped me out with this and
other questions your knowledge in invaluable and cannot be gained
strictly thru books, I will continue to use this mailing list and will
help where I can.



Chris Hood  
Investigator Verizon Global Security Operations Center 
Email: christopher.l.hood@stripped 
Desk: 972.399.5900        

Verizon Proprietary 

NOTICE - This message and any attached files may contain information
that is confidential and/or subject of legal privilege intended only for
the use by the intended recipient.  If you are not the intended
recipient or the person responsible for delivering the message to the
intended recipient, be advised that you have received this message in
error and that any dissemination, copying or use of this message or
attachment is strictly forbidden, as is the disclosure of the
information therein.  If you have received this message in error please
notify the sender immediately and delete the message. 

-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped] 
Sent: Wednesday, July 28, 2004 8:41 AM
To: Christopher L. Hood
Cc: mysql@stripped
Subject: Re: Indexed Database still slow

I think the UNION is the right way to handle this, in fact, I would be 
tempted to break it into 6 UNIONS... more on that later.

You really should concentrate mostly on coverage for fields used in your

WHERE clauses, in this case: Framed_IP_Address and Date. Additional
fields 
can be used to get data straight from the index but the docs say that
they 
must be numeric (not character based). So, in the case of this query, 
those additional fields just make your index larger which takes longer
to 
search. 
Try a two-field index and just this part of your subquery:

Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address 
from PRTC_DIALUP 
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND
PRTC_DIALUP.Date 
= 'one-date-here'

If I am right, that should return somewhere <2 seconds. This means that
a 
6-way union would return in somewhere near or below 12 seconds.  In this

case each query is doing an exact match on an index and the 6 queries 
unioned together should take less time than your 2 3-way queries. I call

them 3 way as each half has to check for one of 3 dates.

Also, if you need to ORDER BY the results of the UNION, you need to 
enclose each participating query in parentheses and put the ORDER BY 
clause after the last query.

I went ahead and expanded your 2-query UNION into a 6-query UNION to 
illustrate:

(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address 
from PRTC_DIALUP 
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' 
        AND PRTC_DIALUP.Date = 'one-date-here'
)
UNION
(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address 
from PRTC_DIALUP 
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' 
        AND PRTC_DIALUP.Date = 'one-day-earlier'
)
UNION
(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address 
from PRTC_DIALUP 
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' 
        AND PRTC_DIALUP.Date = 'one-day-later'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address 
from PRTC_DSL 
Where PRTC_DSL.Framed_IP_Address = 'someipaddress' 
        and PRTC_DSL.Date = 'one-date-here'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address 
from PRTC_DSL 
Where PRTC_DSL.Framed_IP_Address = 'someipaddress' 
        and PRTC_DSL.Date = 'one-day-earlier'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address 
from PRTC_DSL 
Where PRTC_DSL.Framed_IP_Address = 'someipaddress' 
        and PRTC_DSL.Date = 'one-day-later'
)
ORDER BY  Full_Name, Time;

I agree that it will take some additional time to parse those 6 queries 
instead of just 2 but I believe that you won't be able to notice the 
difference.  I would compare those 4 extra queries to the # of queries
per 
second your system handles now to get a rough estimate of the additional

overhead involved.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



christopher.l.hood@stripped wrote on 07/28/2004 08:25:36 AM:

> Ok, I will be the first to say that I am learning about indexes,
however
> it is my understanding that if I have a database with MANY rows and I
> wish my queries to be faster I should index my database. With that
being
> said, I have 2 tables in my database that are being queried with a
> single query using a UNION these 2 tables combined are about 9 Million
> records (yes I said million). 
> 
> My query which is below takes about 1 minute to run, now some people
> would say that this isn't long, however when the 2 tables were sub 5
> million it only took a matter of about 20 seconds to run, so I figure
I
> need an index. So I have created an index called "Main" within both
> tables and added 6 columns to that index, most of the columns that are
> used in my query.
> 
> Sorry for the long background, but here is the problem, my query DID
NOT
> speed up at all. It still takes right at 1 minute per query, so
indexing
> didn't buy me anything as far as I can tell.
> 
> Can someone tell me how the indexes are supposed to be done ( to
ensure
> that I did it correctly) and tell me if they think that it should have
> sped up or if there is a more efficient way to do my query.
> 
> 
> ###QUERY HERE ###
> 
> Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
> PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
> PRTC_DIALUP.Framed_IP_Address 
> from PRTC_DIALUP 
> Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND
> (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date =
> 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') 
> UNION 
> Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
> PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address 
> from PRTC_DSL 
> Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date
=
> 'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date
=
> 'one-day-later') 
> order by Full_Name, Time;
> 
> ### END QUERY ###
> 
> Chris Hood 
> Investigator Verizon Global Security Operations Center 
> Email:  <mailto:christopher.l.hood@stripped>
> christopher.l.hood@stripped 
> Desk: 972.399.5900 
> 
> Verizon Proprietary 
> 
> NOTICE - This message and any attached files may contain information
> that is confidential and/or subject of legal privilege intended only
for
> the use by the intended recipient.  If you are not the intended
> recipient or the person responsible for delivering the message to the
> intended recipient, be advised that you have received this message in
> error and that any dissemination, copying or use of this message or
> attachment is strictly forbidden, as is the disclosure of the
> information therein.  If you have received this message in error
please
> notify the sender immediately and delete the message. 
> 

Thread
Indexed Database still slowchristopher.l.hood28 Jul
  • Re: Indexed Database still slowBrent Baisley28 Jul
    • Re: Indexed Database still slowSGreen28 Jul
  • Re: Indexed Database still slowSGreen28 Jul
    • Re: Indexed Database still slowDan Nelson28 Jul
  • Re: Indexed Database still slowEgor Egorov29 Jul
RE: Indexed Database still slowchristopher.l.hood28 Jul