ignorance? database lookup times

I've got a table, with only 500,000 rows, and I'm searching a char(60) field using select url from demourl where url MATCHES '*deja*' This query is taking almost 20 seconds to complete, to me this seems to be taking too long, is there anything I can do to speed this up? This is only an AMD K6-2 300MHz with 256mb ram and a 7200rpm drive. This will be moving to a Sun Enterprise 250 dual 300MHz Ultrasparc-IIi with 512mb ram for production usage, will I notice a large speed increase with that transition? Any help or suggestions would be appriciated. Thanks, -Alec Sent via Deja.com http://www.deja.com/ Before you buy.

In article <850fee$ivi$1@nnrp1.deja.com>, biggiealec@my-deja.com writes >I've got a table, with only 500,000 rows, and I'm searching a char(60) >field using select url from demourl where url MATCHES '*deja*' > This cannot use an index and so will be slow. Need to remove the initial * to make it fast. >This query is taking almost 20 seconds to complete, to me this seems to >be taking too long, is there anything I can do to speed this up? This >is only an AMD K6-2 300MHz with 256mb ram and a 7200rpm drive. This >will be moving to a Sun Enterprise 250 dual 300MHz Ultrasparc-IIi with >512mb ram for production usage, will I notice a large speed increase >with that transition? > >Any help or suggestions would be appriciated. > >Thanks, > >-Alec > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams

biggiealec@my-deja.com wrote: > I've got a table, with only 500,000 rows, and I'm searching a char(60) > field using select url from demourl where url MATCHES '*deja*' > > ... This sort of query will cause a sequential scan of the table. If the leading * cannot be removed and performance of this sort of query is important , you could speed it up by removing this column from the original table and storing it separately along with the primary key - the sequential scan will need to process fewer pages. Makes the application code a little more tricky. Inserts and deletes will be slower (updates could be faster) but it could be worth it especially if the parent table has a large row-size and the response time is important. (I had a case where this sort of vertical splitting of the table improved query performance by a factor of 10 with little impact on inserts and deletes) A detached index on the column should have helped, but, for some reason, the Optimizer doesn't do the smart thing - an index-scan (maybe you want to test this with your version). Rudy