List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 28 2004 2:33pm
Subject:Re: Indexed Database still slow
View as plain text  
Brent,

I humbly disagree with your analysis.  I believe that it is well 
established in this list and other places 
(http://dev.mysql.com/doc/mysql/en/EXPLAIN.html) that the query engine 
only uses at most 1 index of the available indexes on any table involved 
in the query. It has also been shown that properly constructed 
multi-column indexes can significantly improve the performance of many 
queries (http://dev.mysql.com/doc/mysql/en/Query_Speed.html (and its 
sub-pages)) 
(http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html). 

Based on this information, I dispute your assertion that by creating 
separate indexes on each column to be searched, christopher.l.hood will 
improve the performance of his query. I also dispute your statement that "
The only time you will create an single index containing multiple columns 
is if you will always be searching on the indexed fields in the order you 
declared them in your index" as a multi-column index can be used to answer 
any query that involves just the first column, just the first and second 
columns, just the first, second, and third columns, etc.  You do not need 
to search on every column of a multi-column index for it to be used to 
resolve a query. 

Also, under certain conditions (numeric columns), a multi-column index 
will also avoid the need for a direct table read to return data. This 
optimizes some queries enormously even though the additional columns may 
never appear in the WHERE clause of the query. 
(http://dev.mysql.com/doc/mysql/en/Where_optimisations.html)

I do agree with you that a multi-column index will NOT aid to resolve any 
queries that search only on the second column, the second and third 
columns, etc. I also agree that if a query is searching for values listed 
first and third in a mutli-column query, that the index will be useful 
only for finding records containing the first value listed in the index. 
The third column value will have to be resolved by a seek of the resultset 
(after the index is applied). 

With greatest respect,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Brent Baisley <brent@stripped> wrote on 07/28/2004 09:17:07 AM:

> Your problem is that you created a single index. An index is just a 
> presorted list of the data. The first column in the index is the most 
> relevant, being sorted by that column first.  If you have an index on 
> State+County+Town, how would you quickly find a town? You can't if you 
> don't know the State or County, because the data is sorted first by 
> State, then by County, then by Town.
> You need to create a separate index for each column you are searching 
> on. The only time you will create an single index containing multiple 
> columns is if you will always be searching on the indexed fields in the 
> order you declared them in your index (i.e. State+County).
> 
> On Jul 28, 2004, at 8:25 AM, christopher.l.hood@stripped wrote:
> 
> > 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.
> >
> >
> -- 
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

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