Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Have the schemalint output distinguish between existing and recommended indexes. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA1: |
4ab3df25f1fee7c8fea19d0c64b3e0e4 |
| User & Date: | dan 2016-02-18 19:10:02 |
Context
|
2016-02-19
| ||
| 07:53 | Fix a couple of bugs in the schemalint code. (check-in: 02fbf699 user: dan tags: schemalint) | |
|
2016-02-18
| ||
| 19:10 | Have the schemalint output distinguish between existing and recommended indexes. (check-in: 4ab3df25 user: dan tags: schemalint) | |
|
2016-02-17
| ||
| 20:06 | Schemalint changes: Avoid creating candidate indexes if a compatible index exists. Do not quote identifiers that do not require it. (check-in: cf0f7eeb user: dan tags: schemalint) | |
Changes
Changes to src/shell_indexes.c.
94 94 IdxScan *pNextScan; /* Next IdxScan object for same query */ 95 95 }; 96 96 97 97 /* 98 98 ** Context object passed to idxWhereInfo() 99 99 */ 100 100 struct IdxContext { 101 + char **pzErrmsg; 101 102 IdxWhere *pCurrent; /* Current where clause */ 102 103 int rc; /* Error code (if error has occurred) */ 103 104 IdxScan *pScan; /* List of scan objects */ 104 105 sqlite3 *dbm; /* In-memory db for this analysis */ 105 106 sqlite3 *db; /* User database under analysis */ 106 107 sqlite3_stmt *pInsertMask; /* To write to aux.depmask */ 108 + i64 iIdxRowid; /* Rowid of first index created */ 107 109 }; 108 110 109 111 /* 110 112 ** Data regarding a database table. Extracted from "PRAGMA table_info" 111 113 */ 112 114 struct IdxColumn { 113 115 char *zName; ................................................................................ 583 585 if( rc==SQLITE_OK ) rc = rc2; 584 586 585 587 *pRc = rc; 586 588 return 0; 587 589 } 588 590 589 591 static int idxCreateFromCons( 590 - sqlite3 *dbm, 592 + IdxContext *pCtx, 591 593 IdxScan *pScan, 592 594 IdxConstraint *pEq, 593 595 IdxConstraint *pTail 594 596 ){ 597 + sqlite3 *dbm = pCtx->dbm; 595 598 int rc = SQLITE_OK; 596 599 if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){ 597 600 IdxTable *pTab = pScan->pTable; 598 601 char *zCols = 0; 599 602 char *zIdx = 0; 600 603 IdxConstraint *pCons; 601 604 int h = 0; ................................................................................ 604 607 for(pCons=pEq; pCons; pCons=pCons->pLink){ 605 608 zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 606 609 } 607 610 for(pCons=pTail; pCons; pCons=pCons->pLink){ 608 611 zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 609 612 } 610 613 611 - /* Hash the list of columns to come up with a name for the index */ 612 614 if( rc==SQLITE_OK ){ 615 + /* Hash the list of columns to come up with a name for the index */ 613 616 int i; 614 617 for(i=0; zCols[i]; i++){ 615 618 h += ((h<<3) + zCols[i]); 616 619 } 617 620 618 621 if( idxIdentifierRequiresQuotes(pScan->zTable) ){ 619 622 zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)"; ................................................................................ 626 629 }else{ 627 630 rc = sqlite3_exec(dbm, zIdx, 0, 0, 0); 628 631 #if 0 629 632 printf("/* %s */\n", zIdx); 630 633 #endif 631 634 } 632 635 } 636 + if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){ 637 + int rc2; 638 + sqlite3_stmt *pLast = 0; 639 + rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg, 640 + "SELECT max(rowid) FROM sqlite_master" 641 + ); 642 + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){ 643 + pCtx->iIdxRowid = sqlite3_column_int64(pLast, 0); 644 + } 645 + rc2 = sqlite3_finalize(pLast); 646 + if( rc==SQLITE_OK ) rc = rc2; 647 + } 633 648 634 649 sqlite3_free(zIdx); 635 650 sqlite3_free(zCols); 636 651 } 637 652 return rc; 638 653 } 639 654 640 655 static int idxCreateFromWhere( 641 - sqlite3*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint* 656 + IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint* 642 657 ); 643 658 644 659 static int idxCreateForeachOr( 645 - sqlite3 *dbm, 660 + IdxContext *pCtx, 646 661 i64 mask, /* Consider only these constraints */ 647 662 IdxScan *pScan, /* Create indexes for this scan */ 648 663 IdxWhere *pWhere, /* Read constraints from here */ 649 664 IdxConstraint *pEq, /* == constraints for inclusion */ 650 665 IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ 651 666 ){ 652 667 int rc = SQLITE_OK; 653 668 IdxWhere *p1; 654 669 IdxWhere *p2; 655 670 for(p1=pWhere->pOr; p1 && rc==SQLITE_OK; p1=p1->pNextOr){ 656 - rc = idxCreateFromWhere(dbm, mask, pScan, p1, pEq, pTail); 671 + rc = idxCreateFromWhere(pCtx, mask, pScan, p1, pEq, pTail); 657 672 for(p2=p1->pSibling; p2 && rc==SQLITE_OK; p2=p2->pSibling){ 658 - rc = idxCreateFromWhere(dbm, mask, pScan, p2, pEq, pTail); 673 + rc = idxCreateFromWhere(pCtx, mask, pScan, p2, pEq, pTail); 659 674 } 660 675 } 661 676 return rc; 662 677 } 663 678 664 679 static int idxCreateFromWhere( 665 - sqlite3 *dbm, 680 + IdxContext *pCtx, 666 681 i64 mask, /* Consider only these constraints */ 667 682 IdxScan *pScan, /* Create indexes for this scan */ 668 683 IdxWhere *pWhere, /* Read constraints from here */ 669 684 IdxConstraint *pEq, /* == constraints for inclusion */ 670 685 IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ 671 686 ){ 687 + sqlite3 *dbm = pCtx->dbm; 672 688 IdxConstraint *p1 = pEq; 673 689 IdxConstraint *pCon; 674 690 int rc; 675 691 676 692 /* Gather up all the == constraints that match the mask. */ 677 693 for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){ 678 694 if( (mask & pCon->depmask)==pCon->depmask ){ ................................................................................ 679 695 pCon->pLink = p1; 680 696 p1 = pCon; 681 697 } 682 698 } 683 699 684 700 /* Create an index using the == constraints collected above. And the 685 701 ** range constraint/ORDER BY terms passed in by the caller, if any. */ 686 - rc = idxCreateFromCons(dbm, pScan, p1, pTail); 702 + rc = idxCreateFromCons(pCtx, pScan, p1, pTail); 687 703 if( rc==SQLITE_OK ){ 688 - rc = idxCreateForeachOr(dbm, mask, pScan, pWhere, p1, pTail); 704 + rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pTail); 689 705 } 690 706 691 707 /* If no range/ORDER BY passed by the caller, create a version of the 692 708 ** index for each range constraint that matches the mask. */ 693 709 if( pTail==0 ){ 694 710 for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){ 695 711 assert( pCon->pLink==0 ); 696 712 if( (mask & pCon->depmask)==pCon->depmask ){ 697 - rc = idxCreateFromCons(dbm, pScan, p1, pCon); 713 + rc = idxCreateFromCons(pCtx, pScan, p1, pCon); 698 714 if( rc==SQLITE_OK ){ 699 - rc = idxCreateForeachOr(dbm, mask, pScan, pWhere, p1, pCon); 715 + rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pCon); 700 716 } 701 717 } 702 718 } 703 719 } 704 720 705 721 return rc; 706 722 } 707 723 708 724 /* 709 725 ** Create candidate indexes in database [dbm] based on the data in 710 726 ** linked-list pScan. 711 727 */ 712 -static int idxCreateCandidates( 713 - sqlite3 *dbm, 714 - IdxScan *pScan, 715 - char **pzErrmsg 716 -){ 728 +static int idxCreateCandidates(IdxContext *pCtx){ 729 + sqlite3 *dbm = pCtx->dbm; 717 730 int rc2; 718 731 int rc = SQLITE_OK; 719 732 sqlite3_stmt *pDepmask; /* Foreach depmask */ 720 733 IdxScan *pIter; 721 734 722 - rc = idxPrepareStmt(dbm, &pDepmask, pzErrmsg, "SELECT mask FROM depmask"); 735 + rc = idxPrepareStmt(pCtx->dbm, &pDepmask, pCtx->pzErrmsg, 736 + "SELECT mask FROM depmask" 737 + ); 723 738 724 - for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 739 + for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 725 740 IdxWhere *pWhere = &pIter->where; 726 741 while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){ 727 742 i64 mask = sqlite3_column_int64(pDepmask, 0); 728 - rc = idxCreateFromWhere(dbm, mask, pIter, pWhere, 0, 0); 743 + rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, 0); 729 744 if( rc==SQLITE_OK && pIter->pOrder ){ 730 - rc = idxCreateFromWhere(dbm, mask, pIter, pWhere, 0, pIter->pOrder); 745 + rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, pIter->pOrder); 731 746 } 732 747 } 733 748 } 734 749 735 750 rc2 = sqlite3_finalize(pDepmask); 736 751 if( rc==SQLITE_OK ) rc = rc2; 737 752 return rc; ................................................................................ 743 758 for(pIter=pScan; pIter; pIter=pNext){ 744 759 pNext = pIter->pNextScan; 745 760 746 761 } 747 762 } 748 763 749 764 int idxFindIndexes( 750 - sqlite3 *dbm, /* Database handle */ 765 + IdxContext *pCtx, 751 766 const char *zSql, /* SQL to find indexes for */ 752 767 void (*xOut)(void*, const char*), /* Output callback */ 753 768 void *pOutCtx, /* Context for xOut() */ 754 769 char **pzErr /* OUT: Error message (sqlite3_malloc) */ 755 770 ){ 771 + sqlite3 *dbm = pCtx->dbm; 756 772 sqlite3_stmt *pExplain = 0; 757 773 sqlite3_stmt *pSelect = 0; 758 774 int rc, rc2; 775 + int bFound = 0; 759 776 760 777 rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql); 761 778 if( rc==SQLITE_OK ){ 762 779 rc = idxPrepareStmt(dbm, &pSelect, pzErr, 763 - "SELECT sql FROM sqlite_master WHERE name = ?" 780 + "SELECT rowid, sql FROM sqlite_master WHERE name = ?" 764 781 ); 765 782 } 766 783 767 784 while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 768 785 int i; 769 786 const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 770 787 int nDetail = strlen(zDetail); ................................................................................ 772 789 for(i=0; i<nDetail; i++){ 773 790 if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ 774 791 int nIdx = 0; 775 792 const char *zIdx = &zDetail[i+13]; 776 793 while( zIdx[nIdx]!='\0' && zIdx[nIdx]!=' ' ) nIdx++; 777 794 sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC); 778 795 if( SQLITE_ROW==sqlite3_step(pSelect) ){ 779 - xOut(pOutCtx, sqlite3_column_text(pSelect, 0)); 796 + i64 iRowid = sqlite3_column_int64(pSelect, 0); 797 + const char *zSql = (const char*)sqlite3_column_text(pSelect, 1); 798 + if( iRowid>=pCtx->iIdxRowid ){ 799 + xOut(pOutCtx, zSql); 800 + bFound = 1; 801 + } 780 802 } 781 803 rc = sqlite3_reset(pSelect); 782 804 break; 783 805 } 784 806 } 785 807 } 786 808 rc2 = sqlite3_reset(pExplain); 787 809 if( rc==SQLITE_OK ) rc = rc2; 788 - if( rc==SQLITE_OK ) xOut(pOutCtx, ""); 810 + if( rc==SQLITE_OK ){ 811 + if( bFound==0 ) xOut(pOutCtx, "(no new indexes)"); 812 + xOut(pOutCtx, ""); 813 + } 789 814 790 815 while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 791 816 int iSelectid = sqlite3_column_int(pExplain, 0); 792 817 int iOrder = sqlite3_column_int(pExplain, 1); 793 818 int iFrom = sqlite3_column_int(pExplain, 2); 794 819 const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 795 820 char *zOut; ................................................................................ 837 862 ** database (handle db). The attached in-memory db (aux) contains 838 863 ** application tables used by the code in this file. */ 839 864 rc = sqlite3_open(":memory:", &dbm); 840 865 if( rc==SQLITE_OK ){ 841 866 rc = sqlite3_exec(dbm, 842 867 "ATTACH ':memory:' AS aux;" 843 868 "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;" 869 + "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;" 844 870 "INSERT INTO aux.depmask VALUES(0);" 845 871 , 0, 0, pzErrmsg 846 872 ); 847 873 } 848 874 849 875 /* Prepare an INSERT statement for writing to aux.depmask */ 850 876 if( rc==SQLITE_OK ){ ................................................................................ 868 894 ** removed. */ 869 895 if( rc==SQLITE_OK ){ 870 896 rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg); 871 897 } 872 898 873 899 /* Create candidate indexes within the in-memory database file */ 874 900 if( rc==SQLITE_OK ){ 875 - rc = idxCreateCandidates(dbm, ctx.pScan, pzErrmsg); 901 + rc = idxCreateCandidates(&ctx); 876 902 } 877 903 878 904 /* Figure out which of the candidate indexes are preferred by the query 879 905 ** planner and report the results to the user. */ 880 906 if( rc==SQLITE_OK ){ 881 - rc = idxFindIndexes(dbm, zSql, xOut, pOutCtx, pzErrmsg); 907 + rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg); 882 908 } 883 909 884 910 idxScanFree(ctx.pScan); 885 911 sqlite3_finalize(ctx.pInsertMask); 886 912 sqlite3_close(dbm); 887 913 return rc; 888 914 } 889 915 890 916