Re: [PHP-DB] 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