Huge table needs configuring

Discussion in 'MySQL' started by NAz2K, Nov 28, 2006.

0
  1. #1
    I've got an sql table with over 1 million rows, queries can take anything from 0 to 10 seconds. I want to reduce this time. I've tried using indexes but with minimal success as I have no idea what i'm doing.

    The mysql official site is just too confusing for me. All I am querying is one column only like "SELECT * FROM table WHERE title LIKE '%free%' AND title LIKE '%antivirus%'"

    Can anyone recommend some things to do? Please don't just reply telling me to use indexes as I have no clue what they do and what I really need to put in the size field.

    Thx all
     
    NAz2K, Nov 28, 2006 IP
  2. jerseychump

    jerseychump Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I have worked with large databases and tables with >1 billion rows and multiple indexes. I can optimize your table for $100.
     
    jerseychump, Nov 28, 2006 IP
  3. NAz2K

    NAz2K Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    How to put this nicely...
    Go fudge yourself :)
     
    NAz2K, Nov 28, 2006 IP
  4. symmetric

    symmetric Peon

    Messages:
    31
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I think the issue you're running into is that MySQL can't use an index given the way you're searching. Your search logic says that you want all rows that contain 'free' or 'antivirus'. MySQL can't use the index b/c the LIKE values start with a wildcard. In order for an index to be used, the search logic would have to be like this..


    SELECT * FROM table WHERE title LIKE 'free%' .....

    Of course, that logic is different b/c it's only asking for rows where title begins with 'free' instead of rows where title contains 'free'.


    Have you considered using full-text search? Here's a resource that might be helpful: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
     
    symmetric, Nov 28, 2006 IP
  5. NAz2K

    NAz2K Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi Symmetric, thanks very much for your reply. Full text search looks like the answer i've been looking for, I'll give it a go in a few hours when I get back.

    Thanks for your time!
     
    NAz2K, Nov 28, 2006 IP
  6. NAz2K

    NAz2K Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thx alot! this has made queries really fast and much more relevant to users!

    Cheers :)
     
    NAz2K, Nov 29, 2006 IP
  7. symmetric

    symmetric Peon

    Messages:
    31
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    No problem. Glad I could help.
     
    symmetric, Nov 29, 2006 IP
  8. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Symmetric is right. Indexing works with sub-parts of a column, as per your index spec. To surround a term with % renders any index useless.

    Here's a little helper so you can predict how well a query will perform:

    explain select....

    ie Just type "explain" before your query and the result will tell you how many rows will be searched, what indexes (if any) will be used, whether the search would require a filesort, use of a temporary file, etc.

    If you can work a way to avoid surrounding terms with %, you can also further optimize query execution times by teaking your server config and also pre-loading your indexes assuming you've got the available RAM for it.
     
    Scolls, Dec 3, 2006 IP