Will doing a sort with nocase be faster than without specifying it??
nocase
nocase
We generally order by on Author, Title, Price. Not necessarily all at once or together..
they are
Author Varchar
Title Varchar
Price Float
queries are dynamically built but to give you an idea one such query could like this.
$sqlq='select ' $return ' from ' $table ' where
AUTHOR like $$qauth and TITLE like $$qtit
and AUTHOR\TITLE like $$qat
and AUTHOR = $$qcauth and TITLE = $$qctit
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS like $$qtopic
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS liker $$qtopicr
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$query
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB liker $$queryr
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$newquery
and BOOKNUM = $$bookbin
and ' $idfield ' = $$bookid
' $inpart '
and USER_ID = $$quserid
and PUBLISHER like $$qpub
and FL_COVER = $$binding
and FL_SIGNED = $$signed
and FL_EDITION = $$first
and FL_DUSTJACKET = $$dj
and TITLE not like $$qtitn
and CATLG_ISBN = $$qisbn
and WORK_ID = $$qwork
and "LANGUAGE" like $$qlang
and LCCL = $$qlccl
and LCCL matches $$qlcclstar
and DEWEY = $$qdewey
and DEWEY matches $$qdeweystar
and DATE_PUB between $$qyear and $$qyearhi
and DATE_PUB = $$qyearonly
and ' $price_column ' between $$qprice and $$qpricehi
' $orderpart>
where $orderpart could look like one of these
<switch $qsort>
<case = 'r'><$orderpart = 'order by sellers.Rating DESC, ' $price_column ' ASC'><sum "" $orderpart><$orderpart = $ret>
<case = 't'><$orderpart = 'order by TITLE ASC'>
<case = 'a'><$orderpart = 'order by AUTHOR ASC'>
<case = 'p'><$orderpart = 'order by ' $price_column ' ASC'><sum "" $orderpart><$orderpart = $ret>
<case = 'd'><$orderpart = 'order by DATE_PUB ASC'>
<case = 'tr'><$orderpart = 'order by TITLE DESC'>
<case = 'ar'><$orderpart = 'order by AUTHOR DESC'>
<case = 'pr'><$orderpart = 'order by ' $price_column ' DESC'><sum "" $orderpart><$orderpart = $ret>
<case = 'dr'><$orderpart = 'order by DATE_PUB DESC'>
<default><$orderpart = ''>
Our indexes look like...
xbooknum books /db/indexes/prod/xbooknum A B 00 BOOKNUM
xmmauth books /db/indexes/prod/xmmauth A M 01 AUTHOR
xmmtitl books /db/indexes/prod/xmmtitl A M 01 TITLE
xbookid books /db/indexes/prod/xbookid A B 01 id
xworkid books /db/indexes/prod/xworkid A B 01 WORK_ID
xlccl books /db/indexes/prod/xlccl A B 01 LCCL
xmmany books /db/indexes/prod/xmmany A M 01 AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB
xuseridrec books /db/indexes/prod/xuseridrec A B 01 ,USER_ID USER_REC
xflsigned books /db/indexes/prod/xflsigned A B 01 FL_SIGNED
xmmlang books /db/indexes/prod/xmmlang A M 01 LANGUAGE
xpublish books /db/indexes/prod/xpublish A M 01 PUBLISHER
xdealerprice books /db/indexes/prod/xdealerprice A B 01 DEALER_PRICE
xconsprice books /db/indexes/prod/xconsprice A B 01 CONSUMER_PRICE
xlibraryprice books /db/indexes/prod/xlibraryprice A B 01 LIBRARY_PRICE
xwholesaleprice books /db/indexes/prod/xwholesaleprice A B 01 WHOLESALE_PRICE
xisbn books /db/indexes/prod/xisbn A B 01 CATLG_ISBN
xlistdate books /db/indexes/prod/xlistdate A B 01 LIST_DATE
xdatepub books /db/indexes/prod/xdatepub A B 01 DATE_PUB
xtopic books /db/indexes/prod/xtopic A M 01 BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS
xretprice books /db/indexes/prod/xretprice A B 01 RETAIL_PRICE
xflcover books /db/indexes/prod/xflcover A B 01 FL_COVER
xfledition books /db/indexes/prod/xfledition A B 01 FL_EDITION
xfldustjacket books /db/indexes/prod/xfldustjacket A B 01 FL_DUSTJACKET
Your textarea window needs to be bigger... ;>)
they are
Author Varchar
Title Varchar
Price Float
queries are dynamically built but to give you an idea one such query could like this.
$sqlq='select ' $return ' from ' $table ' where
AUTHOR like $$qauth and TITLE like $$qtit
and AUTHOR\TITLE like $$qat
and AUTHOR = $$qcauth and TITLE = $$qctit
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS like $$qtopic
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS liker $$qtopicr
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$query
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB liker $$queryr
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$newquery
and BOOKNUM = $$bookbin
and ' $idfield ' = $$bookid
' $inpart '
and USER_ID = $$quserid
and PUBLISHER like $$qpub
and FL_COVER = $$binding
and FL_SIGNED = $$signed
and FL_EDITION = $$first
and FL_DUSTJACKET = $$dj
and TITLE not like $$qtitn
and CATLG_ISBN = $$qisbn
and WORK_ID = $$qwork
and "LANGUAGE" like $$qlang
and LCCL = $$qlccl
and LCCL matches $$qlcclstar
and DEWEY = $$qdewey
and DEWEY matches $$qdeweystar
and DATE_PUB between $$qyear and $$qyearhi
and DATE_PUB = $$qyearonly
and ' $price_column ' between $$qprice and $$qpricehi
' $orderpart>
where $orderpart could look like one of these
<switch $qsort>
<case = 'r'><$orderpart = 'order by sellers.Rating DESC, ' $price_column ' ASC'><sum "" $orderpart><$orderpart = $ret>
<case = 't'><$orderpart = 'order by TITLE ASC'>
<case = 'a'><$orderpart = 'order by AUTHOR ASC'>
<case = 'p'><$orderpart = 'order by ' $price_column ' ASC'><sum "" $orderpart><$orderpart = $ret>
<case = 'd'><$orderpart = 'order by DATE_PUB ASC'>
<case = 'tr'><$orderpart = 'order by TITLE DESC'>
<case = 'ar'><$orderpart = 'order by AUTHOR DESC'>
<case = 'pr'><$orderpart = 'order by ' $price_column ' DESC'><sum "" $orderpart><$orderpart = $ret>
<case = 'dr'><$orderpart = 'order by DATE_PUB DESC'>
<default><$orderpart = ''>
Our indexes look like...
xbooknum books /db/indexes/prod/xbooknum A B 00 BOOKNUM
xmmauth books /db/indexes/prod/xmmauth A M 01 AUTHOR
xmmtitl books /db/indexes/prod/xmmtitl A M 01 TITLE
xbookid books /db/indexes/prod/xbookid A B 01 id
xworkid books /db/indexes/prod/xworkid A B 01 WORK_ID
xlccl books /db/indexes/prod/xlccl A B 01 LCCL
xmmany books /db/indexes/prod/xmmany A M 01 AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB
xuseridrec books /db/indexes/prod/xuseridrec A B 01 ,USER_ID USER_REC
xflsigned books /db/indexes/prod/xflsigned A B 01 FL_SIGNED
xmmlang books /db/indexes/prod/xmmlang A M 01 LANGUAGE
xpublish books /db/indexes/prod/xpublish A M 01 PUBLISHER
xdealerprice books /db/indexes/prod/xdealerprice A B 01 DEALER_PRICE
xconsprice books /db/indexes/prod/xconsprice A B 01 CONSUMER_PRICE
xlibraryprice books /db/indexes/prod/xlibraryprice A B 01 LIBRARY_PRICE
xwholesaleprice books /db/indexes/prod/xwholesaleprice A B 01 WHOLESALE_PRICE
xisbn books /db/indexes/prod/xisbn A B 01 CATLG_ISBN
xlistdate books /db/indexes/prod/xlistdate A B 01 LIST_DATE
xdatepub books /db/indexes/prod/xdatepub A B 01 DATE_PUB
xtopic books /db/indexes/prod/xtopic A M 01 BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS
xretprice books /db/indexes/prod/xretprice A B 01 RETAIL_PRICE
xflcover books /db/indexes/prod/xflcover A B 01 FL_COVER
xfledition books /db/indexes/prod/xfledition A B 01 FL_EDITION
xfldustjacket books /db/indexes/prod/xfldustjacket A B 01 FL_DUSTJACKET
Your textarea window needs to be bigger... ;>)
nocase
Which are the most common fields to order on? If PRICE is common, and one of the fields is the most commonly searched it may be worth a compound index. To your like queries ever need post processing? I see you don't have METAMORPH INVERTED index.
Which fields are you selecting? If you are selecting the fields you want to order by, and not some of the larger fields you might want to try using e.g. ORDER BY 1 to order by the first field selected. That may improve performance in some cases, although you would need to try with sample queries to see.
Which fields are you selecting? If you are selecting the fields you want to order by, and not some of the larger fields you might want to try using e.g. ORDER BY 1 to order by the first field selected. That may improve performance in some cases, although you would need to try with sample queries to see.
John Turnbull
Thunderstone Software
Thunderstone Software