Mysql – Better understand table indexing best practices

index-tuninginnodbMySQLmysql-5.6performancequery-performance

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider:

The tables are not that big, I think (1000 – 10,000 rows).
The searches are made from the CMS admin pages – So they include more types of filtering/sorting options/combinations than the frontend.
I'm wondering if there is such thing as over-indexing where I'll add too much indexes that the searches might be quicker, but it will impact dramatically on the performances of inserting/updating data to the table, or that the DB engine will choose the wrong index for a query.
I'm adding Fulltext index when it come to textual searches.
I'm also adding asc/desc indexes for DATETIME/INT/BIGINT for what I believe will give better performances when it comes to sorting/filtering based on columns.

I'm just not sure what will be the optimal point in which adding an index will improve the performances of my App, when I take into consideration the above parameters, and my tendency to be an "Index Nazi" (Pardon my French) – I'm finding myself adding more and more indexes for every possible scenario I can think of.

I do hope that this question is not too broad, and I didn't include an example because it's more general behaviour of good and bad practices when it come to indexing.

Best Answer

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook.

InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s).

WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are different than two separate indexes: INDEX(a), INDEX(b).

Don't use BIGINT unless you really expect to have huge numbers.

DESC is ignored on an INDEX declaration. However, that does not stop the optimizer from running through an index in reverse order to have the effect of 'descending'.

FULLTEXT is useful only if you use MATCH(...) AGAINST(...).

"Too many" indexes slows down inserts some, and slows down UPDATEs that change indexed columns.

Don't index "flags"; such indexes will 'never' be used.

Related Solutions

Postgresql – Guidelines on best indexing strategy for varying searches on 20+ columns

Capture the queries that people actually make.
Analyze data from #1; then devise short, multi-column, indexes (2, maybe 3, columns each)
No. Especially not for yes/no flags. It's ok to combine such, as in #2.
See #3.
The order of columns is important in an index. A 5-column index is (roughly) equivalent to the 1-col index on its first column, but is not useful if you don't query on that column.
Probably.
Forcing into memory is counter-productive because it takes away space for caching other stuff.

More discussion: http://mysql.rjweb.org/doc.php/eav

Mysql might have too many indexes

Rather than try to guess, you should download Percona Tools.

You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

If you not sure, look at the content of the index removal SQL script first

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
vi -R ${ETL_INDX_SCRIPT}

If the resulting script has no ALTER TABLE commands, you don't need anything done.

If there are ALTER TABLE commands and if you are ready, run the script with this

mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

Best Answer

Related Solutions

Postgresql – Guidelines on best indexing strategy for varying searches on 20+ columns

Mysql might have too many indexes

GIVE IT A TRY !!!

Related Question