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