CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmindatabase-administration
GABBY
GABBY

Reputation: 13

MySQL work with the database contains a table with millions of rows

I am not a DBA guy so I want to ask you a few questions.

I have 48 million rows in my table, with 10 columns. On 7 of these columns I need to run queries like :

select * from `table1` WHERE `Flag` = '1' - 10 millions of rows (takes like 2 seconds) -  on Flag column i have INDEX 

select * from table WHERE `name` LIKE '%John%' - 10k results (takes like 10H) on `name column i have INDEX

Well I'm having problems working with LIKE, I need to get the results really fast without even waiting 2 seconds, in my case it takes 10h.

I hope someone here will help me, thank you guys.

Upvotes: 1

Views: 79

Answers (1)

M0rtiis
M0rtiis

Reputation: 3774

Mysql will use your index on LIKE before the first wildcard (%). So if the wildcard is a first symbol of "seach-string", no index will be used.

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

LIKE 'John%' - fast LIKE '%John%' - slow

i believe u should use full-text search

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 1

Related Questions