Videos from the Index-Journal 6:24. Index-Journal celebrates 100 years. Jan 28, 2019; The Lakelands' leading source for news was founded in 1919 and has been publishing ever since.
In docs for various ORMs they always provide a way to create indexes, etc. They always mention to be sure to create the appropriate indexes for efficiency, as if that is inherent knowledge to a non-hand-written-SQLer who needs to use an ORM. My understanding of indexes (outside of PK) is basically: If you plan to do
LIKE
queries (ie, search) based on the contents of a column, you should use a full text index for that column. What else should I know regarding indexes (mostly pertaining to efficiency)? I feel like there is a world of knowledge at my door step, but there's a huge folded mouse pad jammed up under it, so I can't get through (I don't know why I felt like I needed to say that, but thanks for providing the couch).orokusaki
![Nov new dbtool index fund Nov new dbtool index fund](http://www.nuuo.com/backend/CKEdit/upload/images/dbtool.jpg)
25.1k4343 gold badges138138 silver badges227227 bronze badges
2 Answers
Think of an index very roughly like the index in the back of a book. It's a totally separate area from the content of the book, where if you are seeking some specific value, you can go to the index and look it up (indexes are ordered, so finding things there is much quicker than scanning every page of the book).
The index entry has a page number, so you can then quickly go to the page seeking your topic. A database index is very similar; it is an ordered list of the relevant information in your database (the field(s) included in the index), with information for the database to find the records which match.
So... you would create an index when you have information that you need to search on frequently. Normal indexes don't help you for 'partial' seeks like LIKE queries, but any time you need to get a set of results where field X has certain value(s), they keep the DBMS from needing to 'scan' the whole table, looking for matching values.
They also help when you need to sort on a column.
Another thing to keep in mind; If the DBMS allows you to create single indexes that have multiple fields, be sure to investigate the effects of doing so, specific to your DBMS. An index that includes multiple fields is likely only to be fully (or at all) useful if all those fields are being used in a query. Conversely, having multiple indexes for a single table, with one field per index, may not be of much (or any) help for queries that are filtering/sorting by multiple fields.
You mentioned Full Text indexes and PKs (Primary Keys). These are different than regular indexes, though they often serve similar purposes.
First, note that a Primary Key is usually an index (in MSSQL, a 'Clustered Index', in fact), but this does not need to be the case specifically. As an example, an MSSQL PK is a Clustered Index by default; clustered indexes are special in that they are not a separate bit of data stored elsewhere, but the data itself is arranged in the table in order by the Clustered Index. This is why a popular PK is an
int
value that is auto-generated with sequential, increasing values. So, a Clustered Index sorts the data in the table specifically by the field's value. Compare this to a traditional dictionary; the entries themselves are ordered by the 'key', which is the word being defined.But in MSSQL (check your DBMS documentation for your information), you can change the Clustered Index to be a different field, if you like. Sometimes this is done on
datetime
based fields.Full Text indexes are different kinds of beasts entirely. They use some of the same principles, but what they are doing isn't exactly the same as normal indexes, which I am describing. Also: in some DBMS's,
LIKE
queries do not use the full text index; special query operators are required.These indexes are different because their intent is not to find/sort on the whole value of the column (a number, a date, a short bit of char data), but instead to find individual words/phrases within the text field(s) being indexed.
They can also often enable searching for similar words, different tenses, common misspellings and the like, and typically ignore noise words. The different way in which they work is why they also may need different operators to use them. (again, check your local documentation for your DBMS!)
Andrew BarberAndrew Barber34.4k1414 gold badges8080 silver badges109109 bronze badges
This answer is Oracle-specific, but the main points in the answers apply to most relational database systems
Community♦
CheeseConQuesoCheeseConQueso3,2262222 gold badges7777 silver badges123123 bronze badges
Not the answer you're looking for? Browse other questions tagged databasedatabase-indexesdatabase-optimization or ask your own question.
Database Optimize & Repair Tool for phpBB
A phpBB extension that will allow you to check, optimize and repair phpBB's MySQL database tables from a phpMyAdmin-like interface in the Administration Control Panel or from the CLI.
Features
- Optimize, Repair and Check tables directly from the ACP or the CLI
- Select individual tables or all tables at once
- Displays table size and table overhead values in the ACP
- Option to safely disable board during the optimize/repair process
- Results are logged to the Admin log
- Enhanced javascript interactions
Minimum Requirements
- phpBB 3.2.0
- PHP 5.4 or higher
- MySQL 4.0.1 or higher (using MyISAM, InnoDB or Archive table types)
Install
- Download the latest validated release.
- Unzip the downloaded release and copy it to the
ext
directory of your phpBB board. - Navigate in the ACP to
Customise -> Manage extensions
. - Find
Database Optimize & Repair Tool
under Disabled Extensions and clickEnable
.
Usage
After installation, you can find the Optimize & Repair Tool in:
ACP -> Maintenance -> Database -> Optimize & Repair
.Use this extension at your own risk! There have been cases with certain shared web hosts where a database table could crash for a very large forum. This extension will perform the same functions on your database as phpMyAdmin, so if you have been using phpMyAdmin on your database with no problems, then it should be safe to use this extension. It is always safest to run a backup of your database before performing any Optimize or Repair functions just in case anything goes wrong.
Note: InnoDB table types do not support the Repair option.
CLI Usage
The Optimize & Repair Tool can also be run from the command line interface inphpBB using the
db:tool
command. The tool will prompt you to choose an operation:Optimize, Repair, or Check.To check, optimize or repair all tables:
$ php bin/phpbbcli.php db:tool
To check, optimize or repair a specific table:
$ php bin/phpbbcli.php db:tool table_name
To disable the board during an operation, use the
--disable-board
or -D
option:$ php bin/phpbbcli.php db:tool --disable-board
For help with the Optimize & Repair Tool command:
$ php bin/phpbbcli.php db:tool --help
Uninstall
- Navigate in the ACP to
Customise -> Manage extensions
. - Click the
Disable
link for Database Optimize & Repair Tool. - To permanently uninstall, click
Delete Data
, then delete thedbtool
folder fromphpBB/ext/vse/
.
License
© 2013 - Matt Friedman