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