Re: Problem with performance!
Posted in 1998
Topics: Performance & Tuning, Server Administration
Matches in an SQL statement is generally speaking something to avoid if at
all possible.
Given your data can be either lower or upper case (koP*, Kop*, etc.) you
may have to just
bite the bullet unless you can CLEANSE you data to be consistent...(i.e.
All Upper Case, etc.)
As it is I would be surprised if the SQL was NOT ignoring the index and
just walking the table.
Try this in isql or dbaccess: (Forgive me if I have the syntax wrong ---
too many SQL databases in my head right now)
set explain on;
SELECT * FROM tablex WHERE field1 MATCHES> '[K,k][O,o][P,p]*'
Then try
SELECT * FROM tablex WHERE field1 MATCHES> 'KOP*'
Check the results from sqlexplain.out for 'cost', index usage, etc.
--
Rob Vorbroker Phone: 513/336-6741
Vorbroker Consulting, Inc. FAX: 513/336-6742
robv@vorbroker.com www.vorbroker.com
marko kopac <marko.kopac@mais.si> wrote in article
<35112472.91C1DAE3@mais.si>...
> Hello!
>
> Could anybody help me!!!
>
> How to make my SQL quicker: SELECT * FROM tablex WHERE field1 MATCHES
> '[K,k][O,o][P,p]*'
>
> Index is made by field1, table contains 34500 records, sql working 30
> seconds. Is that normal?
>
>
> Marko
>
>