Re: Building indexes

This all helps. I think I need to spend some time experimenting. Thanks for your help! - Ron On 3/19/2010 3:56 PM, Bastien Koert wrote:

[snip]

Here's an example of a tough query (there are lots of Smiths!). SELECT voter1.County, voter1.First Name, voter1.Middle Name, voter1.Last Name, voter1.Residential ZipCode,voter1.House Number, voter1.Mailing Address, voter1.Street Name, voter1.Residential City, voter1.Voter ID, voter1.Congressional Distri,voter1.idVoter, voter1.County FROM voter1 WHERE voter1.County LIKE '%' AND voter1.`Last Name LIKE 'Smith%' AND voter1.First Name` LIKE 'John%' AND voter1.Residential ZipCode LIKE '%' LIMIT 0, 10; Do you think the LIKE '%' hurts for the fields we don't have search criteria? We do that to keep the query definition simple and flexible.

[/snip] Yes, those hurt and will cause you endless grief. A much better course of action is to build the SQL dynamically to only query on the fields where you have values to start with. If I alter your example SELECT voter1.County, voter1.First Name, voter1.Middle Name, voter1.Last Name, voter1.Residential ZipCode,voter1.House Number, voter1.Mailing Address, voter1.Street Name, voter1.Residential City, voter1.Voter ID, voter1.Congressional Distri,voter1.idVoter, voter1.County FROM voter1 WHERE voter1.Last Name LIKE 'Smith%' AND voter1.First Name LIKE 'John%' LIMIT 0, 10; This produces a much cleaner SQL and simpler query for the DB to use. Also, I tend to prefer not using spaces in the field names. I prefer to have an underscore to avoid any issues if I ever have to move databases