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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 8:41 AM To: Christopher L. Hood Cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]> > [EMAIL PROTECTED] > 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. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
