very major performance improvement
----------------------------------
Key: CORE-5407
URL: http://tracker.firebirdsql.org/browse/CORE-5407
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Environment: n.a. (does not matter)
Reporter: Ray Holme
the below query can be very slow or very fast depending on an index
select .... from ... where bla_bla <not> in (select XXXX from yyy)
if tabel yyy has in index with XXXX as the leading value, this query is
blazingly fast, it not and the table is significant (test case 44k rows), it is
turtle slow.
I can either create an index and do the query (overall time for BOTH operations
way below the original query without the index).
or
I can create another table with XXXX as the only column; populate it with
(select distinct XXXX from yyy); index it; and use the new temp table for the
query.
- again MUCH faster for alll of this than the original query.
---- SO
If the query optimizer sees such a query where the cardinality of the table is
greater than some number (say 10) and there is no usable index,
IT SHOULD BUILD A TEMPORARY INDEX FOR THE QUERY.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
