order by
Vladimir Runtu on Informix OnLine 7.2 (SCO) found a MATCHES query on a 100,000-row table took 3 seconds but 15 minutes with ORDER BY. Rudy Fernandes suspected a corrupt or inefficient index on that column and advised disabling and re-enabling indexes and running UPDATE STATISTICS HIGH. Jonathan Leffler advised comparing SET EXPLAIN plans. Leonids Voroncovs asked for the explain output. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello!
I have Informix Online 7.2 running on SCO OpenServer 5
Query
SELECT * FROM seosed
WHERE onimi MATCHES "*LIISING*"
takes about 3 seconds to complete.
and the resultset contains about 50 records.
Table seosed contains about 100 000 records
but
SELECT * FROM seosed
WHERE onimi MATCHES "*LIISING*"
ORDER BY onimi
takes about 15 minutes. What should I do
to make this query complete quicker?
best wishes,
Vlads
Your first SQL is doing a sequential scan (which is the fastest way of
reading the entire contents of a table). Your second SQL is, most
probably, using an index that has onimi as a lead column. This index
could be corrupt or inefficient. Try the following :
1. Rebuild (disable/enable) the index that has onimi as the lead column.
If possible rebuild all your indexes on that table - if one has a
problem, its possible that others are affected to -
SET INDEXES, CONSTRAINTS FOR seosed DISABLED;
SET INDEXES, CONSTRAINTS FOR seosed ENABLED;
2. Update statistics on that column - I'm not sure if it will help.
UPDATE STATISTICS HIGH FOR TABLE seosed(onimi);
Rudy
Vladimir Rüntü wrote:
> Hello!
>
> I have Informix Online 7.2 running on SCO OpenServer 5
>
> Query
>
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*">
> takes about 3 seconds to complete.
> and the resultset contains about 50 records.
> Table seosed contains about 100 000 records
>
> but
>
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*"
> ORDER BY onimi>
> takes about 15 minutes. What should I do
> to make this query complete quicker?
> Vlads
Vladimir Rüntü wrote:
> I have Informix Online 7.2 running on SCO OpenServer 5
>
> Query
>
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*">
> takes about 3 seconds to complete.
> and the resultset contains about 50 records.
> Table seosed contains about 100 000 records
>
> but
>
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*"
> ORDER BY onimi>
> takes about 15 minutes. What should I do
> to make this query complete quicker?
I'd look at the two query plans (SET EXPLAIN ON) -- which no-one has
mentioned yet.
Then I'd review the table and index definitions and the status of UPDATE
STATISTICS as everyone else suggested.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
Vladimir Rьntь wrote:
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*">
> takes about 3 seconds to complete.
Are You sure that query completes?
May be only first row appears?
> SELECT * FROM seosed
> WHERE onimi MATCHES "*LIISING*"
> ORDER BY onimi>
> takes about 15 minutes. What should I do
> to make this query complete quicker?
Can You send us explain file for both queries?