Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


17 - Use LIKE 'xx%'

SQL Anywhere will use an index to implement LIKE 'xx%' if one exists, but the same cannot be said for expressions such as LIKE '%xx' and Left ( column-name, 2 ) = 'xx'. Figure 17 compares several logically similar queries and shows that only one of them uses the index.

Figure 17 - LIKE 'xx%' Outshines LIKE '%xx', Right(), Substr() And Left() 
CREATE TABLE query1

   ( primary1 INTEGER NOT NULL,

     search1 CHAR(10) NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX iquery1 ON query1 ( search1 );


SELECT * FROM query1 WHERE search1 LIKE '%.5';

   Estimated 5000 rows in query ( I/O estimate 247)

   PLAN> query1 (seq)


SELECT * FROM query1 WHERE Right ( search1, 2 ) = '.5';

   Estimated 500 rows in query ( I/O estimate 200)

   PLAN> query1 (seq)


SELECT * FROM query1 WHERE search1 LIKE '5.%';

   Estimated 1 rows in query ( I/O estimate 2)

   PLAN> query1 (iquery1)


SELECT * FROM query1 WHERE Substr ( search1, 1, 2 ) = '5.';

   Estimated 500 rows in query ( I/O estimate 200)

   PLAN> query1 (seq)


SELECT * FROM query1 WHERE Left ( search1, 2 ) = '5.';

   Estimated 500 rows in query ( I/O estimate 200)

   PLAN> query1 (seq)

[Home] [Table of Contents] [Previous Section] [Next Section]