Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder student's Avatar
    Join Date
    Jan 2007
    Posts
    129
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Avoiding mysql slow queries

    Hello,
    I have a mysql database
    Our server is: 64 bit processor server running 4 CPUs with 4 GB of RAM...

    when I checked mysql slow queries log, I found so many lines like this with query _time around 6 to 12 for most of the queries:

    -----------
    # Tue Mar 20 03:02:33 2007
    # Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
    use trials_data;
    SELECT id, site, rank, title, description, keywords, category, reviews, ratings, clicks FROM table WHERE site = 'firstsite' LIMIT 1
    -------------
    # Tue Mar 20 05:01:21 2007
    # Query_time: 4 Lock_time: 0 Rows_sent: 10 Rows_examined: 5210
    use trials_data;
    SELECT DISTINCT site FROM table WHERE ( site='shopping' OR title LIKE '%shopping%' OR keywords LIKE '%shopping%' OR description LIKE '%shopping%') ORDER BY rank LIMIT 40, 10
    -------------
    # Tue Mar 20 05:08:36 2007
    # Query_time: 17 Lock_time: 7 Rows_sent: 1 Rows_examined: 5012
    use trials_data;
    SELECT COUNT(DISTINCT site) FROM cb_urls15 WHERE ( site='recipes' OR title LIKE '%recipes%' OR keywords LIKE '%recipes%' OR description LIKE '%recipes%')
    -----------
    Here
    id=primary key
    site=index
    category=index

    title, description, keyword, reviews are 'text' with around 300 characters each.
    others are of VARCHAR

    I dont understand why I am getting these queries slow though Rows_sent: 1 and Rows_examined: 1

    Please inform me how to optimize these queries.

    Thank you.

  • #2
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    site='recipes' OR title LIKE '%recipes%' OR keywords LIKE '%recipes%' OR description LIKE '%recipes%'
    That's bad right there.

    When you use LIKE on a text field that has a lot of text in it (300 is a good chunk), with 5000 rows it has to go through each field, in each row, for each LIKE.

    If you're trying to do searches, google for 'mysql fulltext'.

  • #3
    Regular Coder student's Avatar
    Join Date
    Jan 2007
    Posts
    129
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thank you Aedrin,
    I have implemented your suggestion, and now my site is running well without considerable slow queries.
    Thanks.


  •  

    Posting Permissions

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