ORDER BY, LIKE !!? (* - new information)
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, 128 RAM, 300 virtual memory, 700 M HDD, 'jfs' is the type
HDD
- postmaster is started with the following parameters :
-i -B 1024 -S -o '-F -S 10240' -D/opt/pgdata
- * an index on 'name' field (but the LIKE don't use indexes - anybody can
obtain this result if use the EXPLAIN command. )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 ?
*Could someone explain me what are the indexes ? They work (properly) only
on WHERE clause with '=', '<', etc. operators ? ( The size of the index file
in ~1/3 * (size of the table file) and if the table is ... the index is also
...)
Thanks,
rex
I do not recall if 6.3 can indexes with the LIKE operator. I know 6.4 does.
In any case, btree indexes are are ordered indexes and may be used when a query
is specifies with a fixed prefix (i.e. 'pattern%'). In this case an ordered
search can be performed. However, if the prefix is a wildcard (i.e. '%pattern'
or '%pattern%') then the btree is of no use. This is because all entries in
the index are possible matches to the pattern.
To illustrate my point: Try looking up a word in the dictionary when you have
no idea what the word starts with.
rex wrote:
Show quoted text
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, 128 RAM, 300 virtual memory, 700 M HDD, 'jfs' is the type HDD - postmaster is started with the following parameters : -i -B 1024 -S -o '-F -S 10240' -D/opt/pgdata - * an index on 'name' field (but the LIKE don't use indexes - anybody can obtain this result if use the EXPLAIN command. )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 ?
*Could someone explain me what are the indexes ? They work (properly) only
on WHERE clause with '=', '<', etc. operators ? ( The size of the index file
in ~1/3 * (size of the table file) and if the table is ... the index is also
...)Thanks,
rex