I have one table. But in phpmyadmin; Something stuck in my head while creating the index. When you select and create more than one column, a single index is created for 3 columns.

How is this different from creating indexes on columns one by one?

Also, which columns makes more sense to create an index for the following query?

TABLE

QUERY

SELECT * FROM table where permalink='example' and status='1'

CodePudding user response：

When you create an index, some operations will be slower (like insert), and you will consume more disk space and memory.

You should create an index on columns with high cardinality (columns with values that are very uncommon or unique) and that you use in WHERE clauses to make them faster.

For example for permalink column you should have an index, but not on status because of low cardinality. If you have 3 columns in your WHERE clause, it could be faster to have a compound index on all 3 columns.

.. WHERE column1 = 'a' AND column2 = 'b' AND column3 = 'c'

For starters, make individually indexes for the columns with high cardinality. After you have lots of data and the system starts to slow down, use the EXPLAIN SELECT ... query to find out that what indexes are used in your query. And then optimize using compound indexes if needed. I use tools like JetProfiler or NewRelic to monitor the system and find slow queries. You cannot anticipate everything from the start. Also, the MySQL slow query log is a good place to find what you need to optimize.

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

CodePudding user response：

Indexing a number i of columns together is done for queries that use combination of all i columns.

Let's say you have table with column1 and column2. You should index both columns (first considering their type and their size) for queries like SELECT * FROM table WHERE column1 = something1 AND column2 = something2.

Keep in mind that this is not always optimal to do (another answer explained why) :)