MySQL Forums
Forum List  »  Performance

Re: how to handle 6 million Records in MY Sql???
Posted by: David West
Date: February 15, 2005 03:59PM

The query you are executing

Select * from books where title like "%america%";

is performing a wildcard search. Indexes will not be used thus a full table scan will be performed.

You need to remove the first occurance of the % from your query to get better results but this will not give you the desired results.

Unless there is some other way you will need to use a different type of database that supports word indexing. I am pretty sure DBText (as rubbish as it is) supports this.

You can however have another table that contains say two fields called BookId and TitleWord and populate this table with all of the words from the titles. There will easily be about 10 times the number of rows in this table but you can then put indexes (maybe also composite indexes) on this new table and run the following query

Select b.* from books b INNER JOIN books_titlewords btw ON b.bookid = btw.bookid where btw.titleword = "america";

I am not sure about the speed of this query with the number of rows in the tables but it might be worth giving it a go.

Goodluck
David

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to handle 6 million Records in MY Sql???
2243
February 15, 2005 03:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.