Optimize mysql table indexes

Question

I have the following situation and want to choose the optimum indexes ( some singles indexes and some multiple indexes).

Row Number : 14,000,000

Table : Add

 CREATE TABLE `Add` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Description` longtext,
  `IDUser` int(11) NOT NULL,
  `DateAdded` datetime(4) DEFAULT NULL,
  `IDCategory` int(11) NOT NULL,
  `RowVersion` datetime NOT NULL,
  `IDStatus` int(11) DEFAULT '3',
  `IDModeration` int(11) DEFAULT '4',
  `Price` double DEFAULT NULL,
  `DateFrom` datetime DEFAULT NULL,
  `DateTo` datetime DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `IDRegion` int(11) DEFAULT NULL,
  `IDCity` int(11) DEFAULT NULL,
  `IDNeighbourhood` int(11) DEFAULT NULL,
  `DateAccepted` datetime(4) DEFAULT NULL,
  `DateUpdated` datetime DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `ShowLandLine` tinyint(1) DEFAULT NULL,
  `DateUpToDated` datetime(4) DEFAULT NULL,
  `Telephone` varchar(50) DEFAULT NULL,
  `UserType` tinyint(1) DEFAULT NULL,
  `Neighbourhood` varchar(255) DEFAULT NULL,
  `IP` varchar(16) DEFAULT NULL,
  `FullEditToken` varchar(32) DEFAULT NULL,
  `IDSource` tinyint(4) DEFAULT NULL,
  `ExpiredAt` datetime DEFAULT NULL,
  `BadWordsFlag` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_Add_DateFrom` (`DateFrom`),
  KEY `IX_Add_IDUser` (`IDUser`),
  KEY `IX_Add_DateUpdated` (`DateUpdated`),
  KEY `IX_Add_DateUpToDated` (`DateUpToDated`),
  KEY `IX_Add_DateTo` (`DateTo`),
  KEY `IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo` (`IDUser`,`IDStatus`,`IDModeration`,`DateFrom`,`DateTo`),
  KEY `IX_Add_DateAdded` (`DateAdded`),
  KEY `IX_Add_Telephone` (`Telephone`),
  KEY `FK_Add_AddModeration` (`IDModeration`),
  KEY `FK_Add_AddStatus` (`IDStatus`),
  KEY `FK_Add_Category` (`IDCategory`),
  KEY `FK_Add_col_city` (`IDCity`),
  KEY `FK_Add_col_neighbourhood` (`IDNeighbourhood`),
  KEY `FK_Add_col_region` (`IDRegion`),
  KEY `IDSource` (`IDSource`),
  KEY `IX_Add_Name` (`Name`),
  KEY `IX_Add_IP` (`IP`),
  KEY `IX_Add_UserType` (`UserType`),
  KEY `IX_Add_IDUser_IDStatus` (`IDUser`,`IDStatus`),
  KEY `FullEditToken` (`FullEditToken`),
  KEY `IX_ExpiredAt` (`ExpiredAt`),
  CONSTRAINT `Add_ibfk_1` FOREIGN KEY (`IDSource`) REFERENCES `AddSource` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_AddModeration` FOREIGN KEY (`IDModeration`) REFERENCES `AddModeration` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_AddStatus` FOREIGN KEY (`IDStatus`) REFERENCES `AddStatus` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_Category` FOREIGN KEY (`IDCategory`) REFERENCES `Category` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_User` FOREIGN KEY (`IDUser`) REFERENCES `User` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_col_city` FOREIGN KEY (`IDCity`) REFERENCES `col_city` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_col_neighbourhood` FOREIGN KEY (`IDNeighbourhood`) REFERENCES `col_neighbourhood` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Add_col_region` FOREIGN KEY (`IDRegion`) REFERENCES `col_region` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=16452993 DEFAULT CHARSET=utf8 |

show index from Add return :

+-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                                               | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Add   |          0 | PRIMARY                                                |            1 | ID              | A         |        1905 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | IX_Add_DateFrom                                        |            1 | DateFrom        | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_IDUser                                          |            1 | IDUser          | A         |        1905 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | IX_Add_DateUpdated                                     |            1 | DateUpdated     | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_DateUpToDated                                   |            1 | DateUpToDated   | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_DateTo                                          |            1 | DateTo          | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo |            1 | IDUser          | A         |        1905 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo |            2 | IDStatus        | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo |            3 | IDModeration    | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo |            4 | DateFrom        | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo |            5 | DateTo          | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_DateAdded                                       |            1 | DateAdded       | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_Telephone                                       |            1 | Telephone       | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FK_Add_AddModeration                                   |            1 | IDModeration    | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FK_Add_AddStatus                                       |            1 | IDStatus        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FK_Add_Category                                        |            1 | IDCategory      | A         |         190 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | FK_Add_col_city                                        |            1 | IDCity          | A         |         635 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FK_Add_col_neighbourhood                               |            1 | IDNeighbourhood | A         |         952 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FK_Add_col_region                                      |            1 | IDRegion        | A         |          63 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IDSource                                               |            1 | IDSource        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_Name                                            |            1 | Name            | A         |        1905 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | IX_Add_IP                                              |            1 | IP              | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_UserType                                        |            1 | UserType        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_Add_IDUser_IDStatus                                 |            1 | IDUser          | A         |        1905 |     NULL | NULL   |      | BTREE      |         |               |
| Add   |          1 | IX_Add_IDUser_IDStatus                                 |            2 | IDStatus        | A         |        1905 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | FullEditToken                                          |            1 | FullEditToken   | A         |         127 |     NULL | NULL   | YES  | BTREE      |         |               |
| Add   |          1 | IX_ExpiredAt                                           |            1 | ExpiredAt       | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
27 rows in set (0.00 sec)

and our major queries are

## most queries used in system

Common part:

SELECT  `a`.`Description` AS `Description`,
        GROUP_CONCAT(CONCAT_WS('=', `catt`.`Name`,
                             IF(`gc`.`Name` IS NOT NULL,
                                `gc`.`Name`, `aav`.`Value`))) AS `Attributes`,
        (( SELECT  IF(`ae`.`IDAdmin` IS NOT NULL, CONCAT('Admin=', `adm`.`adminName`),
                       IF(`ae`.`IDUser` IS NOT NULL, CONCAT('USER=', `ae`.`IDUser`), '') )
                FROM  `AddEvent` AS `ae`
                LEFT JOIN  `Admin` AS `adm`  ON `ae`.`IDAdmin` = `adm`.`adminId`
                WHERE  `a`.`ID` = `ae`.`IDAdd`
                ORDER BY  `ae`.`AddedAt` DESC
                LIMIT  1)
         ) AS `AdminOrUser`,
        'accepted' AS `AddStatus`,
        `a`.`ID` AS `ID`,
        `a`.`Name` AS `Name`, `a`.`IDStatus` AS `IDStatus`, `a`.`BadWordsFlag` AS `BadWordsFlag`,
        `a`.`Price` AS `Price`, `u`.`TelephonePrimary` AS `Telephone`,
        `a`.`DateTo` AS `DateTo`,
        IF(`a`.`DateTo`, `a`.`DateTo`, `a`.`DateAdded`) AS `CustomDate`,
        `a`.`IDModeration` AS `IDModeration`,
        ( ( SELECT  1
                FROM  `AddChanges` AS `ac`
                WHERE  `ac`.`IDAdd` = `a`.`ID`
                  AND  `ac`.`IDModeration` = 4
                ORDER BY  `ac`.`ID` DESC
                LIMIT  1) ) AS `HasChange`,
        ( ( SELECT  GROUP_CONCAT(CONCAT_WS('>', `ai`.`Path`, `ai`.`IsDefault`))
                    FROM  `AddImage` AS `ai`
                    WHERE  `ai`.`IDAdd` = `a`.`ID`) ) AS `Images`,
        `ca2`.`Name` AS `ParentCategory`,
        `ca1`.`Name` AS `Category`,
        `r`.`Name` AS `RegionName`,
        `ct`.`Name` AS `CityName`,
        `n`.`Name` AS `NeighbourhoodName`,
        `u`.`Email` AS `Email`
    FROM  `Add` AS `a`
    LEFT JOIN  `User` AS `u`         ON `u`.`ID` = `a`.`IDUser`
    LEFT JOIN  `Category` AS `ca1`   ON `ca1`.`ID` = `a`.`IDCategory`
    LEFT JOIN  `Category` AS `ca2`   ON `ca2`.`ID` = `ca1`.`IDParent`
    LEFT JOIN  `col_region` AS `r`   ON `r`.`Id` = `a`.`IDRegion`
    LEFT JOIN  `col_city` AS `ct`    ON `ct`.`Id` = `a`.`IDCity`
    LEFT JOIN  `col_neighbourhood` AS `n`     ON `n`.`Id` = `a`.`IDNeighbourhood`
    LEFT JOIN  `AddAttributeValues` AS `aav`  ON `aav`.`IDAdd` = `a`.`ID`
    LEFT JOIN  `CategoryAttribute` AS `catt`  ON `catt`.`ID` = `aav`.`IDCategoryAttribute`
    LEFT JOIN  `GenericCollection` AS `gc`    ON `gc`.`ID` = `aav`.`Value`

#1 detailed view

    WHERE (`a`.`DateTo` > CURRENT_TIMESTAMP()
      AND  `a`.`IDStatus` = 1
      AND  `a`.`IDModeration` = 1 )
      AND (`a`.`IDStatus` <> 2)
    GROUP BY  `a`.`ID`
    ORDER BY  `a`.`ID` DESC
    LIMIT  51

# detailed with state

    WHERE  ((`a`.`DateTo` > CURRENT_TIMESTAMP()
                      AND  `a`.`IDStatus` = 1
                      AND  `a`.`IDModeration` = 1
            )
              AND  (`a`.`IDStatus` <> 2)
           )
      AND  (`a`.`IDRegion` = '8')
    GROUP BY  `a`.`ID`
    ORDER BY  `a`.`ID` DESC
    LIMIT  51

# detailed with category

    WHERE  ((`a`.`DateTo` > CURRENT_TIMESTAMP()
                      AND  `a`.`IDStatus` = 1
                      AND  `a`.`IDModeration` = 1
            )
              AND  (`a`.`IDStatus` <> 2)
           )
      AND  (`a`.`IDCategory` = '43627'
              OR  `ca1`.`IDParent` = '43627'
              OR  `ca1`.`IDParent` IN (
                SELECT  `ca3`.`ID` AS `ID`
                    FROM  `Category` AS `ca3`
                    WHERE  `ca3`.`IDParent` = '43627')
           )
    GROUP BY  `a`.`ID`
    ORDER BY  `a`.`ID` DESC
    LIMIT  51

#detailed with date category state

    WHERE  (((((`a`.`DateTo` > CURRENT_TIMESTAMP()
                                              AND  `a`.`IDStatus` = 1
                                              AND  `a`.`IDModeration` = 1)
                                      AND  (`a`.`IDStatus` <> 2)
                          )
                              AND  (UNIX_TIMESTAMP(`a`.`DateAdded`) >= '1503430200'))
                      AND  (UNIX_TIMESTAMP(`a`.`DateAdded`) <= '1505071799')
            )
              AND  (`a`.`IDCategory` = '43627'
                      OR  `ca1`.`IDParent` = '43627'
                      OR  `ca1`.`IDParent` IN (
                        SELECT  `ca3`.`ID` AS `ID`
                            FROM  `Category` AS `ca3`
                            WHERE  `ca3`.`IDParent` = '43627'))
           )
      AND  (`a`.`IDRegion` = '8')
    GROUP BY  `a`.`ID`
    ORDER BY  `a`.`ID` DESC
    LIMIT  51

# summary mode

SELECT  'accepted' AS `AddStatus`, `a`.`ID` AS `ID`, `a`.`Name` AS `Name`,
        `a`.`IDStatus` AS `IDStatus`, `a`.`BadWordsFlag` AS `BadWordsFlag`,
        `a`.`Price` AS `Price`, `u`.`TelephonePrimary` AS `Telephone`,
`a`.`DateTo` AS `DateTo`,
IF(`a`.`DateTo`, `a`.`DateTo`, `a`.`DateAdded`) AS `CustomDate`,
`a`.`IDModeration` AS `IDModeration`,
    ( ( SELECT  1
            FROM  `AddChanges` AS `ac`
            WHERE  `ac`.`IDAdd` = `a`.`ID`
              AND  `ac`.`IDModeration` = 4
            ORDER BY  `ac`.`ID` DESC
            LIMIT  1)) AS `HasChange`,
    ( ( SELECT  GROUP_CONCAT(CONCAT_WS('>', `ai`.`Path`, `ai`.`IsDefault`))
            FROM  `AddImage` AS `ai`
            WHERE  `ai`.`IDAdd` = `a`.`ID`)
    ) AS `Images`, `ca2`.`Name` AS `ParentCategory`,
        `ca1`.`Name` AS `Category`, `r`.`Name` AS `RegionName`,
        `ct`.`Name` AS `CityName`, `n`.`Name` AS `NeighbourhoodName`,
        `u`.`Email` AS `Email`
    FROM  `Add` AS `a`
    LEFT JOIN  `User` AS `u`  ON `u`.`ID` = `a`.`IDUser`
    LEFT JOIN  `Category` AS `ca1`  ON `ca1`.`ID` = `a`.`IDCategory`
    LEFT JOIN  `Category` AS `ca2`  ON `ca2`.`ID` = `ca1`.`IDParent`
    LEFT JOIN  `col_region` AS `r`  ON `r`.`Id` = `a`.`IDRegion`
    LEFT JOIN  `col_city` AS `ct`  ON `ct`.`Id` = `a`.`IDCity`
    LEFT JOIN  `col_neighbourhood` AS `n`  ON `n`.`Id` = `a`.`IDNeighbourhood`
    WHERE  (((((`a`.`DateTo` > CURRENT_TIMESTAMP()
      AND  `a`.`IDStatus` = 1
      AND  `a`.`IDModeration` = 1)
      AND  (`a`.`IDStatus` <> 2))
      AND  (UNIX_TIMESTAMP(`a`.`DateAdded`) >= '1503430200'))
      AND  (UNIX_TIMESTAMP(`a`.`DateAdded`) <= '1505071799')
            )
      AND  (`a`.`IDCategory` = '43627'
              OR  `ca1`.`IDParent` = '43627'
              OR  `ca1`.`IDParent` IN (
                        SELECT  `ca3`.`ID` AS `ID`
                            FROM  `Category` AS `ca3`
                            WHERE  `ca3`.`IDParent` = '43627'))
           )
      AND  (`a`.`IDRegion` = '8')
    ORDER BY  `a`.`ID` DESC
    LIMIT  51

I'm not sure about indexes and how many indexes and on which field or fields is optimum for our case, as I mentioned before we have 14 million Ads records on our Website.

update

we are looking for optimizing table indexes according to our queries, we appreciate any clue or solution to achieve.

Explain of query :

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: range
possible_keys: PRIMARY,IX_Add_DateFrom,IX_Add_IDUser,IX_Add_DateUpdated,IX_Add_DateUpToDated,IX_Add_DateTo,IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo,IX_Add_DateAdded,IX_Add_Telephone,FK_Add_AddModeration,FK_Add_AddStatus,FK_Add_Category,FK_Add_col_city,FK_Add_col_neighbourhood,FK_Add_col_region,IDSource,IX_Add_Name,IX_Add_IP,IX_Add_UserType,IX_Add_IDUser_IDStatus,FullEditToken,IX_ExpiredAt
          key: FK_Add_AddStatus
      key_len: 5
          ref: NULL
         rows: 1480
        Extra: Using index condition; Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.a.IDUser
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: ca1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.a.IDCategory
         rows: 1
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: ca2
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.ca1.IDParent
         rows: 1
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: r
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.a.IDRegion
         rows: 1
        Extra: NULL
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: ct
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.a.IDCity
         rows: 1
        Extra: NULL
*************************** 7. row ***************************
           id: 1
  select_type: PRIMARY
        table: n
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.a.IDNeighbourhood
         rows: 1
        Extra: NULL
*************************** 8. row ***************************
           id: 1
  select_type: PRIMARY
        table: aav
         type: ref
possible_keys: IX_AddAttributeValues_IDAdd
          key: IX_AddAttributeValues_IDAdd
      key_len: 4
          ref: trumpet.a.ID
         rows: 1
        Extra: NULL
*************************** 9. row ***************************
           id: 1
  select_type: PRIMARY
        table: catt
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.aav.IDCategoryAttribute
         rows: 1
        Extra: NULL
*************************** 10. row ***************************
           id: 1
  select_type: PRIMARY
        table: gc
         type: eq_ref
possible_keys: PRIMARY,IX_GenericCollectiON_ON_IDType_INCLUDE_ID_Name_IDCategory_SortOr
          key: PRIMARY
      key_len: 4
          ref: trumpet.aav.Value
         rows: 1
        Extra: Using where
*************************** 11. row ***************************
           id: 4
  select_type: DEPENDENT SUBQUERY
        table: ai
         type: ref
possible_keys: IX_AddImage_IDAdd
          key: IX_AddImage_IDAdd
      key_len: 4
          ref: func
         rows: 1
        Extra: NULL
*************************** 12. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: ac
         type: ref
possible_keys: IDAdd,IDModeration
          key: IDAdd
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where; Using filesort
*************************** 13. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: ae
         type: ref
possible_keys: IDAdd
          key: IDAdd
      key_len: 4
          ref: func
         rows: 3
        Extra: Using where; Using filesort
*************************** 14. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: adm
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: trumpet.ae.IDAdmin
         rows: 1
        Extra: NULL
14 rows in set (0.00 sec)

Answer 1

this is not exact Query just a pseudo-code

Then we cannot necessarily give you the best indexes. For example, it makes a big difference whether that LONGTEXT is part of the SELECT *. This is because the value is probably an extra disk hit away.

Left join to Location,Left join to User

I'm pretty sure the comma is a syntax error. And without the ON clause, I suspect that the query is incomplete.

Please qualify all columns in the sample code by which table the are in. For example, in query #1, it makes a huge difference whether status and PostDate are in the same table.

There is no status or PostDate in the one table you provided; please provide the other tables.

status = 2 may be optimizable, but status <> 2 is more complex. Which is it?

I see

... AND  `a`.`IDStatus` = 1
... AND  (`a`.`IDStatus` <> 2)  -- Get rid of this?

Please address those issues.

Meanwhile, here's a few tentative answers

INDEX(status, PostDate) -- in that order (#1)

INDEX(IDModeration, IDStatus, DateTo)  -- DateTo last (in big query)

Find the 51 IDs first, _then_ `LEFT JOIN` to the other stuff.

Change this:

SELECT  1
                    FROM  `AddChanges` AS `ac`
                    WHERE  `ac`.`IDAdd` = `a`.`ID`
                      AND  `ac`.`IDModeration` = 4
                    ORDER BY  `ac`.`ID` DESC
                    LIMIT  1

to

EXISTS ( SELECT 1 FROM AddChanges
           WHERE IdAdd = a.ID
             AND IDModeration = 4 )

A messier issue that would help some: Many of the datatypes in use are unnecessarily large. For example, do you really need up to 2 billion Regions? Shrinking the dataypes would shrink the dataset, which would make it more cacheable, hence less I/O, hence faster.

Single-column indexes on low-cardinality columns (IDModeration, IDStatus, UserType) tend to never be used.

Over-normalization can be costly (IDModeration, IDStatus). That is, what is the advantage of having a table of statuses rather than an ENUM of the strings? Also, ENUM is 1 byte versus 4 bytes for INT.

Size

With 64GB of RAM and 14GB of data, innodb_buffer_pool_size should be set to more than 14GB, but no more than 50GB; certainly not a tiny 128MB. And set innodb_buffer_pool_size = 16 at that time. Those changes will provide some amount of performance, perhaps a lot. (It would be hard to be specific about how much improvement.)

Specific queries

For #1 detailed view. Replace the current single-column index on IDStatus with

INDEX(IDStatus, IDModeration, DateTo, ID)

For detailed with state. Replace single-column index on IDRegion with:

INDEX(IDRegion, IDStatus, IDModeration, DateTo, ID)

For detailed with category, the first index will work somewhat. But also change

OR `ca1`.`IDParent` IN (
            SELECT  `ca3`.`ID` AS `ID`
                FROM  `Category` AS `ca3`
                WHERE  `ca3`.`IDParent` = '43627')

to

OR EXISTS ( SELECT 1 FROM `Category` AS `ca3`
                WHERE  ca1.ID = ID
                  AND  ca1.IDParent = 43627 )

and Category needs INDEX(IDParent, ID) unless it currently has PRIMARY KEY(ID).

For detailed with date category state, do not "hide a column in a function". In particular, change UNIX_TIMESTAMP(a.DateAdded) >= '1503430200' to

a.DateAdded >= FROM_UNIXTIME(1503430200)

Or (equivalently)

a.DateAdded >= '2017-08-22 12:30:00'

For summary mode, the above fixes will probably help.

Is that all that can be done? I doubt it. But, before digging deeper, I would like you to implement what you can of the above changes. Then we can incrementally work on whatever seems best as the next step.

When providing EXPLAIN, please use ;, not \G; I would rather struggle with "too wide" than "too high".

You are at the verge of hitting the StackOverflow limit of amount of text in a Question.

Answer 2

create index idx_add
on Ads(LocationID,status,PostDate,Id);

i think this indexes are enough if all other table are joined using primary key and you can also create create index on Location.state & Category.name

Answer 3

These queries look optimized already. You can probably create temporary tables with indexes and join them, instead of making subqueries. I doubt that playing with indexes will give you significant change in performance.

The only thing I would do here, if I was desperate for optimization, is to materialize queries. Looks like the parameters which change are region, category, date range and I doubt you really care about few minutes of lag when it comes to the date range.

So materialize your queries and fetch away with the max sql speed?