MySQL query question

Participant ,
Oct 12, 2018 Oct 12, 2018

Copy link to clipboard

Copied

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?

Views

382

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Oct 14, 2018 Oct 14, 2018
weezerboy  wroteI have a name field in my database like this -table has 15M rows'Jones, Albert Louis'Where the field is like thislast name, first name, middle nameI have a cf search that the user can enter a first name and last name and a middle name that searches that fieldI have everything indexed properly
If there is just one search form-field, then I will suggest<cfquery>SELECT *FROM tableWHERE name LIKE <CFQUERYPARAM VALUE="%#form.searchname#%" CFSQLType="CF_SQL_VARCHAR"></cfquery>If there a...

Likes

Translate

Translate
LEGEND ,
Oct 12, 2018 Oct 12, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Oct 14, 2018 Oct 14, 2018

Copy link to clipboard

Copied

LATEST

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines