[concorde]

I am talking about Multi-Condition Search feature of my datafeed site. There are so many products (ovsertock, buy.com, j&r, compusa....) in my database and I want to add a Multi-Condition Search feature to my site. The product table looks like this:

id
product name
price
short description
shipping fee
brand
produc category
....

Almost all these fields can be conditions of search. For example, a customer want to find a portable DVD Player which price is less than $500 and the merchant offers free shipping, the mysql query will be:

SELECT id, name, description, picture, price FROM table_name WHERE name like '%portable DVD Player%' AND shipping_fee=0 AND price < 500 LIMIT 0, 20

This is only a sample, btw. There are other possible conditions. My question is, how to optimize my database? How to create index on this table?

[frank3iii]

The way I do something like this is to use phpMyadmin.

Select the table which you plan to use from the left menu. In the right pane, select the column identifier (say...product_name). Move over to the right hand section where you see the lightening bolt, key, etc. Click on the key. Do that for each column you wish to search.

This will make each selected column an index in your table. MySql searches indexes much faster than if it had to search the entire db. You can achieve time savings of 50% or more on multi-search queries.

Someone else may have a different/better method.

Frank

[concorde]

But... does index work for query like "LIKE '%keyword%' and 'price <500'?

[concorde]

And... for such a large index, I have to provide enough memory for mysql...

[frank3iii]

Originally Posted by concorde

....But... does index work for query like "LIKE '%keyword%' and 'price <500'?.....And... for such a large index, I have to provide enough memory for mysql...

Yes. And Yes.

On one of my larger datafeed sites I have *one* table with 103k plus records worth 99,902 kb of data. On that table I have six indexes. I use it to run specific LKE '%xxx%' AND WHERE column name NOT LIKE '%...%' . On one of my category pages there are thirteen different LIKE/ NOT LIKE queries in one sql statement.

You can get very, very, specific data output this way.

Frank

[concorde]

Frank, If I execute the query using

SELECT ... WHERE name like '%portable DVD Player%' AND shipping_fee=0 AND price < 500 LIMIT 0, 20

Should I create an index on all these 3 fields (name, shipping_fee, price) or I need to create 3 indexes for each fields?

[frank3iii]

Originally Posted by concorde

Frank, If I execute the query using

SELECT ... WHERE name like '%portable DVD Player%' AND shipping_fee=0 AND price < 500 LIMIT 0, 20

Should I create an index on all these 3 fields (name, shipping_fee, price) or I need to create 3 indexes for each fields?

You only need one index per field. So for the above you would index name, shipping, and price.

Something else to consider: You can exclude data just as easily as you can include it. Makes for very individual pages. Maybe something like this:

SELECT id, product name, price, description, shipping fee, brand, product category FROM products WHERE name LIKE '%portable DVD Player%' AND shipping_fee=0 AND price < 500 AND description NOT LIKE '%240volt%' AND category NOT LIKE '%accessories%' ORDER BY name ASC LIMIT 0, 20

In this case, you'd want description and category to be indexed also.

Hope this is helpful.

Frank

[chetf]

If you are doing plenty of likes - check full text indexing, it will greatly reduce the time it takes to return data sets.

Chet

[concorde]

That is to say, I need to create N different indexes for N query conditions. The full text indexing is for Text field, right? The name of my table is a varchar(128)

[frank3iii]

Originally Posted by concorde

That is to say, I need to create N different indexes for N query conditions.

Not sure what you mean, but I'll take another shot at it.

N query conditions does not enter in to it.

You only need to index a particular field one time. No matter what or how many queries you run against the table, you only need a particular field indexed once. (I don't think you can index the same field more than once.)

But, you need to index *each* field that you plan on running LIKE and NOT LIKE
queries on. At least that is true on both of my different MySQL servers.

Where are all the real gurus that I know frequent this forum? Give us some help here, please.

Frank

[MichaelColey]

I found this to be one of the best resources for optimizing SQL:

http://dev.mysql.com/tech-resources/...719/index.html

It covers just about everything.

[Leader]

Originally Posted by concorde

That is to say, I need to create N different indexes for N query conditions. The full text indexing is for Text field, right? The name of my table is a varchar(128)

When I started using fulltext indexing on a site of mine, I just changed my varchar fields (that I wanted to use in the index) into Text fields, and presto...

[concorde]

Leader I think you are right. According to Michael Coley's URL:

When MySQL uses indexes
When you use a LIKE that doesn't start with a wildcard.
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

That's to say, mysql does NOT use indexes if the LIKE starts with wildcard. For example, the following query does not use indexes at all:

SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'

The Michael's page also said that

When MySQL uses indexes
When all columns used in the query are part of one key.

For example, a key has three part: key1(category, price, free_shipping)
And the following query will use index:

SELECT * FROM table WHERE category=1 AND price < 500 AND free_shipping=1

But how about the followings:

1. SELECT * FROM table WHERE category=1 AND free_shipping=1
(The customer does not have requirement about price)

2. SELECT * FROM table WHERE name like '%dvd player' AND category=1 AND price<500 AND free_shipping=1

(name is FULL Text index)

Are they use indexes? All I want to do is avoiding full raw select and make the query as fast as possible. The key problem is different customers will use different query conditions, which makes the indexes are hard to be desgined.

[chetf]

Do not use like with full text indexes, or really any index, it should always be avoided. Like is the slowest operation you can do. So if it is a small table, fine, but a big data feed? ugh.

Take your select statement and put "explain" in front of it. So for the one example above,

EXPLAIN SELECT * FROM table WHERE name like '%dvd player' AND category=1 AND price<500 AND free_shipping=1

This will show you what indexes are being used. You should see it is not using your fulltext indexes. Fulltext indexes, to use correctly will need a little more complicated calling.

Here is the listing for help:
http://dev.mysql.com/doc/mysql/en/fulltext-search.html

You can use the boolean or non-boolean method, but if you want the true power of fulltext, you want to go boolean, but from your examples you look like you just want simple scoring.

Chet