List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 27 1999 3:41am
Subject:Re: Multiple indexed searches mistery
View as plain text  
At 3:57 PM +0100 1999-12-26, Chris M¸ller wrote:
>Hello list,
>I am new to mySQL and new to SQL, too. I have printed out the very
>comprehensive docs and keep reading them again and again as I go. I have
>mySQL 3.22.27 installed.
>I have successfully installed a db of 500,000+ records which has apart
>from 80 other fields 10 individual fields containing search criteria. I
>have indexed all of these. When I search each individual field for the
>same search criterium, each search takes less than one second!
>(Typically 0.2 secs! Great!) However, the same search criterium may be
>found in any of the 10 fields. I therefore search for "Architect%" in
>each of the 10 fildes individually in less than 6 secs total. If,
>however, I pack may search of the ten fields in one OR search, it takes
>one minute and 8 secs! The OR search is:
>SELECT * FROM  producst where searchcrit0 like "Architect%" OR
>searchcrit1 like "Architect%" OR ... searchcrit9 like "Architect%";
>In this case, the search takes 1 min and 8 secs!
>From the docs I understand that if all fields are indexed, the OR should
>use the indexes.
>Am I missing something? Is there a workaround, abetter way to do this?

If you read in the manual how indexes are used, you will also find that
OR-ing together conditions prevents indexes from being used efficiently.
You'll probably get better results by running several independent queries,
each of which searches for one of the terms that you're OR-ing together.
If you want to present the results all at once or in a particular order,
you can retrieve them into a temporary table, then select from that table.
This will also allow you to eliminate duplicates in the result.

>I would greatly appreciate any hints to help me solve this mistery.
>Another question:
>My search results are presented via the rather slow Internet. I notice
>that the search results are only presented when the search is completed.
>Is there a way to force mySQL to spill-out the results gradually, i.e.
>as they are being built. The idea is to start transmitting as soon as
>possible, even if the search is not completed, since transmission will
>last longer than the time to complete the search. This could shorten the
>time from dispatching the search to its presentation on the Browser.

This depends on whether your search interface allows you to specify
that the query should use mysql_use_result() rather than mysql_store_result()
to return the results.  DBI will, for example, but PHP will not.  However,
if you decide to use multiple independent queries (as outlined above),
this won't do you any good -- but that may not matter because your performance
will be increased.

Paul DuBois, paul@stripped
Multiple indexed searches misterycjmueller26 Dec
  • Re: Multiple indexed searches misteryPaul DuBois27 Dec
  • Re: Multiple indexed searches misterySven E. van 't Veer27 Dec
  • Re: Multiple indexed searches mistery (solved!)cjmueller27 Dec
    • Re: Multiple indexed searches mistery (solved!)Paul DuBois27 Dec
Re: Multiple indexed searches misteryAndy30 Dec
  • Re: Multiple indexed searches misterySasha Pachev30 Dec