Speed up MySQL query

[dubya]

Hey all,

I have a database of around 400,000 business listings, with 30 or so fields.

When a user searches by zipcode and category, the search sometimes takes a minute or two based on the amount of data in that area.

Are there any corners I could cut, or simple queries I could run to speed up the process?

Any help, or resources would be great.

Thanks,
Dave

 

[psalzmann]

When a user searches by zipcode and category, the search sometimes takes a minute or two based on the amount of data in that area.

Hi Dave,

Try adding INDEXES to the `zipcode` and `category` fields. This will help considerably.

Regards,
Peter

 

[dubya]

That seemed to work just fine.

Thanks, +rep!

 

[paaaaaaaaaa]

Also if your database had become fragmented from deletes you should use the optomise table tool.

 

[allabout42]

I would assume mysql is similar to postgre in that you can change the size of your buffer pool. You probably want it about 2/3 of your total memory so that it'll increase the chances of a hit while not hindering your overall performance. Adding additonal indexes will also help as psalzmann stated.