hi i am using MYSQL Database with PHP, i have a table of "locations" with the data of almost 15,00,000 Records (Size 260 MB) , when i make the search in that table then it take long time to search and also take long time to display results , can any one give me idea how can i optimize this table or just divide this table into multiple tables or any thing else


Your suggestion are highly appreciated

No idea

we would need to see the existing structure(s) and queries before even having a clue why it's slow to start with.

Do you have any indexes on your table, or are you forcing the server to search 260 MB every time you run a query? If you do have indexes, are they being used

We would need to see the table structure(s), the query, and ideally the execution plan to really diagnose anything.

If the db type is "MyISAM" you can set up indexes. To set up an index the fields to be used can be varchar or text type fields. The total length of an index field is 255.

So, lets say you're going to build an index called for_search and in it you'll use
`location`
`ID`
`city`

You can assign value length of the field as 85 for each of these in the index `for_search`

I have indexes set up on mine too because I have a search used on my directory and the fields included in the index are the site title, description, and keywords.

Then when a search is performed using my search script it uses the search index I have assigned those fields to. Then based on the information it dynamically pulls the categories, size, index date, etc., to display in the SERP along with the site title, description, and so forth.

Then I took it a step further and added an algorithm to determine relevance based on the factors stated above. Next to each return in the SERP a relevance factor is displayed.

Hope this helps.

hi Thanks for your replies,

The db type is "MyISAM"

Please Deb can you send me the sample code (algorithm )


Many Thanks

Sorry, the algorithm isn't public source. I wrote it and it's one of the things that makes my directory unique to all of the others. So, I can't give you my codes. But, you'd probably be able to do some research on writing algorithms and come up with something equally as useful, all on your own.

Try to divide results and use pagination, just like google <prev 1,2,3,4,5 Next>

Faisal, if you need help with pagination, just ask. I can post codes for you to do this quite easily. Then if you run into any trouble, or it isn't working properly -- just post your code snippets and I'll help you through it.

Doing pagination isn't really hard per se, but it can be a pain in the butt sometimes.