I have a 50MB +- table with about 70,000 records which on which I was
doing LIKE selects. It was taking approx 20 secs to complete the search.
The table is something like...
CREATE TABLE info ( lastname char(50), street_name char(50), ...(etc omitted)
);
CREATE INDEX nx_info1 ON info (lastname);
CREATE INDEX nx_info2 ON info (street_name);
on which I was doing...
SELECT * FROM info WHERE street_name LIKE 'MAIN%';
...this would take about 20 secs to complete. Because the wildness only
happens at the end of the search string, I changed the query to...
SELECT * FROM info WHERE substring( street_name from 1 to 4 ) = 'MAIN';
...this takes under 2 secs. I wrote a piece of code in python to do this
automatically for me but it seems to me that the parser/optimizer could
take a look at this case and re-write the query with the '=' instead of
the 'LIKE'. I've looked through the 'C' code to see where this could
happen but it is too thick for me to sort out with my schedule, so I
thought I'd make the suggestion here.
cheers
jim
--
If everything is coming your way then you're in the wrong lane.
