Search code examples
mysqlindexingpercona

Select SQL Query Not Working With Simple Where clouse

Thee is one table Mysql Table On which simple sql where date = 'Some date' is not working

Have checked logs. Reload the tables several times & tried.

This is proof that record exists :-

select * from TRN_RP_CONSUMPTION_DAILY limit 1;

+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+
| TRCD_DATE  | TRCD_SPREAD_START_DATE | TRCD_SPREAD_END_DATE | TRCD_SOURCE_CHENNEL_ID | TRCD_CIRCLE_ID | TRCD_CATALOUGE_ID | TRCD_CONTENT_ID | TRCD_SONG_CODE_ID | TRCD_YT_CP_POLICY_ID | TRCD_YT_CHANNEL_ID | TRCD_PRODUCT_NAME_ID | TRCD_TRXN_TYPE_ID | TRCD_TRXN_COUNT | TRCD_TOP_LINE | TRCD_REVENUE |
+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+
| 2018-01-01 | 2018-01-01             | 2018-01-04           |                      5 |              1 |                 1 |          945723 |                 1 |                    1 |                  1 |                  211 |               180 |            1.75 |             0 |            0 |
+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+

This is proof that index on date exists :-

select * from TRN_RP_CONSUMPTION_DAILY limit 1;
CREATE TABLE `TRN_RP_CONSUMPTION_DAILY` (
  `TRCD_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SPREAD_START_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SPREAD_END_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SOURCE_CHENNEL_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_CIRCLE_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_CATALOUGE_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_CONTENT_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_SONG_CODE_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_YT_CP_POLICY_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_YT_CHANNEL_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_PRODUCT_NAME_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_TRXN_TYPE_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_TRXN_COUNT` double NOT NULL DEFAULT '0',
  `TRCD_TOP_LINE` double NOT NULL DEFAULT '0',
  `TRCD_REVENUE` double NOT NULL DEFAULT '0',
  KEY `IDX_TRCD_DATE` (`TRCD_DATE`),
  KEY `IDX_TRCD_SOURCE_CHENNEL_ID` (`TRCD_SOURCE_CHENNEL_ID`),
  KEY `IDX_TRCD_CATALOUGE_ID` (`TRCD_CATALOUGE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is the issue, it should give some count but its not coming (See 1st query result above data is present):-

select count(*) from TRN_RP_CONSUMPTION_DAILY where TRCD_DATE='2018-01-01';

+----------+
| count(*) |
+----------+
|        0 |
+----------+

Proof that it is using index :-

explain select count(*) from TRN_RP_CONSUMPTION_DAILY where TRCD_DATE='2018-01-01';
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table                    | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | TRN_RP_CONSUMPTION_DAILY | ref  | IDX_TRCD_DATE | IDX_TRCD_DATE | 3       | const |    1 | Using index |
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+

Force index also not working :-

select count(*) from TRN_RP_CONSUMPTION_DAILY FORCE INDEX(IDX_TRCD_DATE) where TRCD_DATE='2018-01-01';
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Yes, its huge table :-

select count(*) from TRN_RP_CONSUMPTION_DAILY;
+------------+
| count(*)   |
+------------+
| 2006275044 |
+------------+

Table & Index Size :-

103G = TRN_RP_CONSUMPTION_DAILY.MYD

52G = TRN_RP_CONSUMPTION_DAILY.MYI

Surpriseingly this is working, but I can not use alway like this :-

select count(*) from TRN_RP_CONSUMPTION_DAILY where date(TRCD_DATE)='2018-01-01';
+----------+
| count(*) |
+----------+
|  1235523 |
+----------+

I know This is working because index will not come in account when using function on that indexed column.

Which Percona Server :-

Server version: 5.5.60-38.12-log Percona Server (GPL), Release 38.12, Revision 26ef816

Nothing comes in Error or warning mysql log when query is giving zero count.

Where clouse on other column on which there is index that is working properly. Can someone help why its not working on that date column? I want to add some more index on this table but this is not working so I am stopping here.

Solution

  • Move from MyISAM to InnoDB.

    Meanwhile, one of these should work. (Go down the list until you get a usable table. Most options are slow because they involve copying the table.)

    • CHECK TABLE TRN_RP_CONSUMPTION_DAILY; reports an error, do REPAIR TABLE TRN_RP_CONSUMPTION_DAILY;.

    • OPTIMIZE TABLE TRN_RP_CONSUMPTION_DAILY;

    • REPAIR TABLE TRN_RP_CONSUMPTION_DAILY USE_FRM;

    • DROP INDEX ... (for each index), then ADD INDEX ...

    • copy table over:

      CREATE TABLE new LIKE real;
INSERT INTO new SELECT * FROM TRN_RP_CONSUMPTION_DAILY;
RENAME TABLE TRN_RP_CONSUMPTION_DAILY TO old,
      new TO TRN_RP_CONSUMPTION_DAILY;
DROP TABLE old;

    • Restore from backup?

    Those are things that are sometimes needed for MyISAM tables; InnoDB is more robust.