Results 1 to 14 of 14
  1. #1
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    How to speed up the mulit-condition query of mysql
    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?

  2. #2
    Sgt. Joe Friday frank3iii's Avatar
    Join Date
    January 18th, 2005
    Posts
    441
    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
    "Just the facts, Ma'am." Sgt. Joe Friday, Dragnet

  3. #3
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    But... does index work for query like "LIKE '%keyword%' and 'price <500'?

  4. #4
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    And... for such a large index, I have to provide enough memory for mysql...

  5. #5
    Sgt. Joe Friday frank3iii's Avatar
    Join Date
    January 18th, 2005
    Posts
    441
    Quote 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
    "Just the facts, Ma'am." Sgt. Joe Friday, Dragnet

  6. #6
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    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?

  7. #7
    Sgt. Joe Friday frank3iii's Avatar
    Join Date
    January 18th, 2005
    Posts
    441
    Quote 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
    "Just the facts, Ma'am." Sgt. Joe Friday, Dragnet

  8. #8
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    If you are doing plenty of likes - check full text indexing, it will greatly reduce the time it takes to return data sets.

    Chet

  9. #9
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    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)

  10. #10
    Sgt. Joe Friday frank3iii's Avatar
    Join Date
    January 18th, 2005
    Posts
    441
    Quote 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
    "Just the facts, Ma'am." Sgt. Joe Friday, Dragnet

  11. #11
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    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.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  12. #12
    Resident Genius and Staunch Capitalist Leader's Avatar
    Join Date
    January 18th, 2005
    Location
    Florida
    Posts
    12,817
    Quote 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...
    There is no knowledge that is not power. ~Hemingway

  13. #13
    Member concorde's Avatar
    Join Date
    January 18th, 2005
    Posts
    109
    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.

  14. #14
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    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

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Featured: How long is your mysql query taking
    By oranges in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: August 31st, 2011, 05:37 PM
  2. Need help with MySQL query
    By mobilebadboy in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 17th, 2007, 03:00 AM
  3. Exporting MYSQL export query to a text file
    By dflsports in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: October 22nd, 2006, 11:49 AM
  4. Help please with mysql query
    By IGshop in forum Programming / Datafeeds / Tools
    Replies: 15
    Last Post: January 19th, 2004, 08:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •