Slow response from SELECT query I have 50 lac rows (i.e. 5 million rows) in student table.

I fire select * from student where s_name like('%something word%') limit 0,5.

I define indexing on s_name column.

That query takes 2 minutes or more sometimes.

I want quick response within 5 seconds or faster for that type of query.

How...?

I tried the below query also:

SELECT * FROM (select * from student  ORDER BY s_name LIMIT 0,10) as s 
WHERE `status` = 'active' AND ( `s_name ` LIKE ('%a%') or `f_name ` LIKE ('%a%')) 
ORDER BY s_name LIMIT 0,5


Code taken and adjusted from: search for something in the first 1000 records in the table
First of All indexes wont be used if used %keyword% , it should be keyword%.

after you do that add index on 

index (status, s_name, f_name)