Re: Building indexes

[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 -- Bastien Cat, the other other white meat