Question 63802 in PhpMyAdmin Question posted in
The official documentation can be found here.

MySQL Multiple Colums Index – Phpmyadmin

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

id username permalink status
2 Example example 1

QUERY

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

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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) 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search