A website's internal search engine stored the following table the number of times each phrase (in lower case form) was searched for:

TABLE searchedPhrases id INTEGER PRIMARY KEY NOT NULL, text VARCHAR(200) NOT NULL, count INTEGER NOT NULL;

Autocomplete suggestions are generated using the folllowing SQL query :

SELECT text FROM searchedPhrases WHERE text LIKE 'input_text%' ORDER BY count DESC;

Note: here input_text is in lower case form.

Write a SQL statement to create an index that will improve the performance of the query.

I know to create index based on specific attribute, but how to create an index that improve the performance, Please help me out.

CodePudding user response：

It depends on which indexing technology you have in the targeted RDBMS.

Because I think it is a school problem, and because I am also professor, I will give you some thoughts...

If B-TREE indexes are used in your DBMS (probably all DBMS uses B-TREE) the perfect index will be :

CREATE INDEX X ON searchedPhrases (text );