Hi all,
I have a table with 253.380 records. It's a firewall log. I thought that
creating an index will improve queries but results so far were
disappointing.
The table has about 20 columns, most of them of type "text". There's a
text field "ip_type" that has one of three values: TCP, UDP, ICMP. I
tried to do two selects with and without an index on "ip_type".
Without index:
"select count(*) from log where ip_type='udp';" takes 3.0 seconds (this
query evaluates to zero rows).
"select count(*) from log where ip_type='UDP';" takes 4.5 seconds (this
query evaluates to 245.182 rows).
With index:
"select count(*) from log where ip_type='udp';" takes 0.0 seconds.
"select count(*) from log where ip_type='UDP';" takes 5.0 seconds.
It looks like creating an index degrades performance if the result set
is similar to the size of the whole table (I had much better results
when the condition was met by only two thousand records). Is this
normal?
--
Milos Prudek