MySQL query question

[weezerboy]

I have a name field in my database like this -table has 15M rows

'Jones, Albert Louis'

Where the field is like this

last name, first name, middle name

I have a cf search that the user can enter a first name and last name and a middle name that searches that field

I have everything indexed properly

When I do this query I get results immediately

select *

FROM table

where

name LIKE 'Jones%' 

But when I add the first name the query never returns results or takes a very long time

Like this

select *

FROM table

where

name LIKE 'Smith%, John%'

Is my syntax correct? Is there a better way to do this query?

[WolfShade]

Hello, weezerboy,

Firstly, I would like to address the issue of using SELECT * in queries; this forces the database to reference a system table to actually get the column names to SELECT from.  Even if you are SELECTing every column in the table, it's better to type out all the column names.  And if you DON'T need every column, then SELECT * is choking bandwidth that needn't be used, as well as CPU processing that would otherwise not be used.

But AFAICT, your query WHERE clause appears correct to my eyes.  % is for zero or more of anything, so it should work.

However, the way that _I_ would have done it is to put first, middle and last names in separate columns, and create a WHERE clause for each type entered.

SELECT first, middle, last, address, username
FROM tableA
WHERE 1=1
      <cfif len(trim(form.first))>AND first LIKE '%#form.first#%'</cfif>
     <cfif len(trim(form.middle))>AND middle LIKE '%#form.middle#%'</cfif>
     <cfif len(trim(form.last))>AND last LIKE '%#form.last#%'</cfif>  /* USING CFQUERYPARAM, OF COURSE */

V/r,

^ _ ^

[BKBK · Accepted solution]

weezerboy  wrote

I have a name field in my database like this -table has 15M rows

'Jones, Albert Louis'

Where the field is like this

last name, first name, middle name

I have a cf search that the user can enter a first name and last name and a middle name that searches that field

I have everything indexed properly

If there is just one search form-field, then I will suggest

<cfquery>

SELECT *

FROM table

WHERE name LIKE <CFQUERYPARAM VALUE="%#form.searchname#%" CFSQLType="CF_SQL_VARCHAR">

</cfquery>

If there are separate search fields for first, middle and last name, then I will suggest

<cfset searchFullname=form.lastname & "%#form.firstname#%" & form.middlename>

<cfquery>

SELECT *

FROM table

WHERE name LIKE <CFQUERYPARAM VALUE="#searchFullname#" CFSQLType="CF_SQL_VARCHAR">

</cfquery>