Re: how to handle 6 million Records in MY Sql???

[David West]

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