0

I am a real newbie with sql and the indexes.

I'd like to know if it's better to use indexes on mutliple columns or multiple index on each columns.

Most of my queries have this structure :

SELECT
       c.app_id,
       c.app_version_id,
       count(DISTINCT dqaid) AS user_open
FROM crashes c
     JOIN reasons r ON c.reason = r.id
WHERE status = 0
     AND datecreated >= (NOW() - INTERVAL 7 DAY)
GROUP BY c.app_id, c.app_version_id

should I put indexes like that :

 * @Table(name="crashes", indexes={
 *     @Index(name="datecreated_idx", columns={"datecreated"}),
 *     @Index(name="app_id_idx", columns={"app_id"}),
 *     @Index(name="app_version_id_idx", columns={"app_version_id"}),
 *     @Index(name="dqaid_idx", columns={"dqaid"}),
 *     })

Or more like that :

 *     @Index(name="app_v_id_idx", columns={"app_version_id", "app_id", "datecreated"}),

I understand what an index is for, but I don't really undestand how they work. Do they work on the select ? On the where ? On the group by ?

Yasuu
  • 165
  • 7
  • The main purpose of an index is to avoid a table scan (where the _entire_ table needs to be searched to match your where clause). Think of an index as a table of sorted values extracted from your main table. It's significantly faster to search a sorted list than an unsorted list. You can only reference one index per instance of the table in your query, so you should be putting all the columns that you are querying against into a single index. You can have multiple indexes per table though, so you might have different indexes for different common queries. – RToyo Jun 02 '17 at 16:06
  • There's a lot to know about indexes, but at the most basic level, you can think of it as "if my query has indexed columns in the where clause, the query will be much faster". [This question](https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes) will give a little insight into how the order of your columns in your index matters. – RToyo Jun 02 '17 at 16:08

0 Answers0