Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Index not getting used in mysql query

Hi,
I have the following table :
CREATE TABLE `note` (
  `id` char(36) NOT NULL,
  `body` text CHARACTER SET utf8mb4,
  `title` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `preview` text CHARACTER SET utf8mb4,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user` varchar(34) DEFAULT NULL,
  `conversation` varchar(34) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


And I performed the following query :
explain select * from note where user='u:ynul5luymmnlys0u' and conversation is not null order by updated desc;

Open in new window

In the output the key column had value NULL
which is expected as there is not index on user column.
So i added an index :
create index user_idx on note (user);

And performed the same query the output still had NULL in key column ??
Although in possible_keys it did show user_idx. Dont know why it didnt use the index on user column.

Please help me in understanding the above behavior.

Thanks
0
Rohit Bajaj
Asked:
Rohit Bajaj
2 Solutions
 
Tomas Helgi JohannssonCommented:
Hi!

See my comment here.

It depends on your data and number of rows in the table what the optimizer does. If your table statistics shows that one user has more than 25% of conversation values then this index would not be enough to filter out the data fast enough and therefore the optimzer chooses tablescan.

Regards,
     Tomas Helgi
0
 
Pawan KumarDatabase ExpertCommented:
You can also force your index on your query if you are sure.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now