List:General Discussion« Previous MessageNext Message »
From:rich johnson Date:June 10 2003 10:10am
Subject:Re: Fw: 2 questions about indexing and testing speed
View as plain text  
>"Mojtaba Faridzad" <mfaridzad@stripped> wrote:
> > I'm learning MySQL and I got two question:
> >
> > 1) As I know, MySQL has a buffer to keep the last records which have 
>been retreived. So if I run a query two times, the second time will be 
>faster. How can I temporarly stop this service? because I want to run 
>different queries and compare their speed and find the best solution for a 
>problem.

In newer versions of mysql (4.0.3+), you can issue

    set global query_cache_limit=0;

in older versions, you need to set the value in my.cnf, then restart the 
server.

> > 2) A table has 2 fields, Active CHAR(1), and Company CHAR(40). Sometimes 
>I want to search a company name and sometimes I want to search an active 
>company. For faster searching, do I need to create two index like these:
> > CREATE INDEX active ON customer (CONCAT(active, company));

No need to specify "concat"

  CREATE INDEX ative ON customer (active, company)

should do.

> > CREATE INDEX company ON customer (company);
> >
> > If I create just the first index, do I still have a fast searching on 
>company name ( without mentioning Active )?  like this:
> > SELECT * FROM customer WHERE company = 'my test company';
>
>Nope. If you have index only on (active, company), it will not be used in 
>the above query.

True.

- Rich

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. 
http://join.msn.com/?page=features/virus

Thread
Fw: 2 questions about indexing and testing speedMojtaba Faridzad9 Jun
  • arrangement of fields in a tableMojtaba Faridzad9 Jun
    • Re: arrangement of fields in a tableJeremy Zawodny9 Jun
    • Re: arrangement of fields in a tableNils Valentin10 Jun
  • Re: Fw: 2 questions about indexing and testing speedVictoria Reznichenko10 Jun
Re: Fw: 2 questions about indexing and testing speedrich johnson10 Jun
  • INT typeMojtaba Faridzad10 Jun
    • Re: INT typeDan Nelson10 Jun
RE: INT typeMike Hillyer10 Jun
  • Re: INT typeMojtaba Faridzad10 Jun
    • Re: INT typePaul DuBois10 Jun
    • Re: INT typeDan Nelson10 Jun