Jump to content

A better way to write this SQL select query


embsupafly

Recommended Posts

This is a SQL query I am using in php, it does not work when I put in the second WHERE clause, other than that it works as desired. Is there a way to write it to where it uses both WHERE clauses, maybe even a way to shorten up the query?

$sql = "SELECT * FROM $inventory_table WHERE year LIKE '%$search%' OR size LIKE '%$search%' OR color LIKE '%$search%' OR gvw LIKE '%$search%' OR brakes LIKE '%$search%' OR vin LIKE '%$search%' OR manufacturer LIKE '%$search%' OR body_type LIKE '%$search%' OR curb_wt LIKE '%$search%' OR comments LIKE '%$search%' WHERE status = 'Available'";
Link to comment
Share on other sites

Yikes! Two things: first, the syntactically correct way to write your query is the following (UNTESTED):

[code]$sql = "SELECT * FROM $inventory_table WHERE ( year LIKE '%$search%' OR size LIKE '%$search%' OR color LIKE '%$search%' OR gvw LIKE '%$search%' OR brakes LIKE '%$search%' OR vin LIKE '%$search%' OR manufacturer LIKE '%$search%' OR body_type LIKE '%$search%' OR curb_wt LIKE '%$search%' OR comments LIKE '%$search%' ) AND status = 'Available'";[/code]

However, this query will be ridiculously slow on a table of any reasonable size -- you should consider adding a FULLTEXT index.

Hope that helps.
Link to comment
Share on other sites

What is the correct way to do a full search query? I set the columns to FULLTEXT indexes in mysql and this is the query that I tried in php:

[code]
$sql = "SELECT * FROM $inventory_table WHERE MATCH(year, size, color, gvw, brakes, vin, manufacturer, body_type, curb_wt, comments) AGAINST ('$search') AND status = 'Available'";
[/code]

This is the error I get:

Can't find FULLTEXT index matching the column list

I have verified that all of the columns are set as FULLTEXT
Link to comment
Share on other sites

According to the MySQL 5.0 Doc, Full-text Restrictions:
The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow.

Basically all the columns need to be part of the same fulltext index. If you have individual fulltext index files for each column, MySQL doesn't combine them (though it should IMO).
Link to comment
Share on other sites

[!--quoteo(post=343507:date=Feb 7 2006, 07:16 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 7 2006, 07:16 PM) [snapback]343507[/snapback][/div][div class=\'quotemain\'][!--quotec--]
That's right -- you need to add all of these columns to a _single_ FULLTEXT index.
[/quote]

Can you explain how to do this? I do it in phpMyAdmin and i just click on the FULLTEXT button for each column. You are saying that I have to create a full text index that has all of the columns in it?
Link to comment
Share on other sites

I have no idea if PHPMyAdmin can even do this. Just drop you one-column FULLTEXT indexes, and add a multi-column one:

[code]ALTER TABLE yourTable ADD FULLTEXT(year, size, color, gvw, brakes, vin, manufacturer, body_type, curb_wt, comments);[/code]

This is quite a few columns -- I hope you're not updating this table too frequently.

Hope that helps.
Link to comment
Share on other sites

Go to the structure page for the table in question. Below the column listing there is a listing of all the indexes.

Type 10 for "Create an index on __ columns".
Click "Go"
Select all 10 columns from the dropdown, in the order you will query them (year, size, etc...)
Click "Save"

Wait for the index to be created (could be a while if the table has a lot of rows).

Now you can use that same index list to delete all the individual fulltext indexes. They are wasting space and speed.
Link to comment
Share on other sites

Thank you everyone, I have been able to create the FULLTEXT index with the multiple columns, however when I do a search it produces no text results:

This is the SQL code that does not work:

[code]
$sql = "SELECT * FROM $inventory_table WHERE MATCH(year, size, color, gvw, brakes, vin, manufacturer, body_type, curb_wt, comments) AGAINST ('$search') AND status = 'Available'";
[/code]

But this code does work:

[code]
$sql = "SELECT * FROM $inventory_table WHERE ( year LIKE '%$search%' OR size LIKE '%$search%' OR color LIKE '%$search%' OR gvw LIKE '%$search%' OR brakes LIKE '%$search%' OR vin LIKE '%$search%' OR manufacturer LIKE '%$search%' OR body_type LIKE '%$search%' OR curb_wt LIKE '%$search%' OR comments LIKE '%$search%' ) AND status = 'Available'";
[/code]

Any ideas why it is producing no results for the FULLTEXT query? There are matching rows...

I know that SQL query is long and horrible, but my only alternatives are FULLTEXT searches right?

This table will be modified on a daily basis with probably 50 different insert/update queries per working day. Is this too much?

Link to comment
Share on other sites

FULLTEXT indexes have been known to behave "funny" but according to spec. For example, certain words are ignored, words less than 4 characters in length are also ignored, and sometimes too many matching rows is also bad. I suggest you take a look at the [a href=\"http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html\" target=\"_blank\"]relevant refman page[/a] as well for further information.

50 isn't that many -- I was just trying to make you aware of the fact that you'll be updating a rather large index. I would argue that this performance hit is probably worth it, especially if your queries on this table largely outweigh the updates.
Link to comment
Share on other sites

[!--quoteo(post=343546:date=Feb 7 2006, 03:55 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 7 2006, 03:55 PM) [snapback]343546[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Go to the structure page for the table in question. Below the column listing there is a listing of all the indexes.

Type 10 for "Create an index on __ columns".
Click "Go"
Select all 10 columns from the dropdown, in the order you will query them (year, size, etc...)
Click "Save"

Wait for the index to be created (could be a while if the table has a lot of rows).

Now you can use that same index list to delete all the individual fulltext indexes. They are wasting space and speed.
[/quote]

thanks wikning1, saved me much head banging of the brick wall variety!
I owe you 1!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.