A better way to write this SQL select query

[embsupafly]

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'";

[fenway]

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.

[embsupafly]

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

[wickning1]

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).

[fenway]

That's right -- you need to add all of these columns to a _single_ FULLTEXT index.

[embsupafly]

[!--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?

[fenway]

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.

[wickning1]

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.

[fenway]

Figures that someone would know how -- BTW, the order of the columns doesn't matter, unlike in most indexes.

[embsupafly]

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?

[fenway]

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.

[finnMacCool]

[!--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!