I've a table with 150 000 records. Table has about 80 columns (INTEGERS, VARCHAR). There are 3 important (to this problem) columns:
ID INTEGER PRIMARY KEY ANAME VARCHAR(250) WITH INDEX ANAME_IDX DEL INTEGER WITH INDEX DEL_IDX DEL = 1 - about 100 records DEL = 0 - about 149 900 records My query is: SELECT ID, ANAME FROM MYTABLE WHERE 1=1 AND ( ANAME LIKE '%TEST%' ) AND (0=1 OR DEL = 0) ORDER BY ANAME It returns about 500 records in 150 seconds : (, and the plan is: PLAN (MYTABLE ORDER ANAME_IDX) What can I do to speed up this query ? Whitout ORDER it runs in about 1.5 s. Maybe some parameters in firebird.conf can be optimized ? Maybe setting Temp direcotory to another HDD ? When the sorting is performed ? Before (150k) or after (500) selecting records ? Firebird SS 2.1.4 x64, on Win2008 x64, DB size - 20GB, lot of RAM Current Firebird.conf changes: CPUAffinityMask=2 DefaultDbCachePages = 262144 MaxFileSystemCache = 0 UdfAccess = Restrict UDF b
