Building indexes
in [Databases]
|
Prev: Calculator
Next: help needed.
From: Bastien Koert on 19 Mar 2010 16:56 [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
From: listread on 20 Mar 2010 12:35
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 > > > |