Thread: ORDER BY, LIKE !!?

ORDER BY, LIKE !!?

From
"rex"
Date:
Hi,

I have :
 - a table with more than 2.000.000 records. It looks like this :

+----------------------------------+----------------------------------+-----
--+
|              Field                    |              Type
|Length |
+----------------------------------+----------------------------------+-----
--+
| fileno                                | int4
|4      |
| size                                  | int4
|4      |
| type                                  | char2
|2      |
| date                                 | datetime
|8      |
| host                                 | varchar()
|32     |
| name(with path)                | varchar()
|1024 |
+----------------------------------+----------------------------------+-----
--+
 - a PostgreSQL 6.3
 - 4.1 AIX system
 - postmaster is started with the following parameters :
     -i -B 1024 -S -o '-F -S 10240' -D/opt/pgdata

I want to select only few (100) rows, [from a given row,] having an order
criterium,
faster (< 2 min) :
" SELECT * FROM File WHERE name LIKE '%/u/projects%' [AND name LIKE ...]
             ORDER BY name;"
 [from the beginning of the row 750000]

                                OR

" SELECT * FROM File WHERE name LIKE '%/u/projects%' [AND name LIKE ...]
             ORDER BY date;"
 [from the beginning of the row 750000]

Could someone help me ?

Thanks,
rex