Skip to main content
W
weezerboy
Inspiring
October 12, 2018
Answered

MySQL query question

  • October 12, 2018
  • 2 replies
  • 545 views

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?

    This topic has been closed for replies.
    Correct answer BKBK

    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>

    2 replies

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    October 14, 2018

    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>

    WolfShade
    WolfShade
    Legend
    October 12, 2018

    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,

    ^ _ ^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices