www.webdeveloper.com
Results 1 to 6 of 6

Thread: Very Large SQL query really slow when querying...How to speed up?

  1. #1
    Join Date
    May 2008
    Location
    Nebraska
    Posts
    32

    Very Large SQL query really slow when querying...How to speed up?

    I have a very large query that takes quite awhile to perform a search. Is there something else I can try or am I just stuck?

    Code:
    SELECT *
    FROM `Article` , `RSS` , `Site`
    WHERE `Article`.PublishedOn <1261001319
    AND `RSS`.SiteID = `Site`.SiteID
    AND `Article`.RSSID = `RSS`.RSSID
    AND (
    (
    upper( `Article`.Title ) LIKE upper( '% html %' )
    OR upper( `Article`.Description ) LIKE upper( '% html %' )
    OR upper( `Site`.Name ) LIKE upper( '% html %' )
    OR upper( `RSS`.Name ) LIKE upper( '% html %' )
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% CSS %' )
    OR upper( `Article`.Description ) LIKE upper( '% CSS %' )
    OR upper( `Site`.Name ) LIKE upper( '% CSS %' )
    OR upper( `RSS`.Name ) LIKE upper( '% CSS %' )
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% Web Developer %' )
    OR upper( `Article`.Description ) LIKE upper( '% Web Developer %' )
    OR upper( `Site`.Name ) LIKE upper( '% Web Developer %' )
    OR upper( `RSS`.Name ) LIKE upper( '% Web Developer %' )
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% ajax %' )
    OR upper( `Article`.Description ) LIKE upper( '% ajax %' )
    OR upper( `Site`.Name ) LIKE upper( '% ajax %' )
    OR upper( `RSS`.Name ) LIKE upper( '% ajax %' )
    )
    )
    ORDER BY `Article`.PublishedOn DESC
    LIMIT 0 , 60

    I would be completely willing to recreate these queries since there could be several dozen called on a single page load.

  2. #2
    Join Date
    May 2008
    Location
    Nebraska
    Posts
    32
    The previous query took 18 seconds to execute. I shortened it to not examine as many fields, to quicken the seach, but it still took 13 seconds...Heres the shortened query:

    Code:
    SELECT * 
    FROM `Article` , `RSS` , `Site` 
    WHERE `Article`.PublishedOn <1261010276
    AND `RSS`.SiteID = `Site`.SiteID
    AND `Article`.RSSID = `RSS`.RSSID
    AND (
    (
    upper( `Article`.Title ) LIKE upper( '% html %' ) 
    OR upper( `Article`.Description ) LIKE upper( '% html %' ) 
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% CSS %' ) 
    OR upper( `Article`.Description ) LIKE upper( '% CSS %' ) 
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% Web Developer %' ) 
    OR upper( `Article`.Description ) LIKE upper( '% Web Developer %' ) 
    )
    OR (
    upper( `Article`.Title ) LIKE upper( '% ajax %' ) 
    OR upper( `Article`.Description ) LIKE upper( '% ajax %' ) 
    )
    )
    ORDER BY `Article`.PublishedOn DESC 
    LIMIT 0 , 54

  3. #3
    Join Date
    May 2008
    Location
    Nebraska
    Posts
    32
    Anyone have any ideas? Right now the database contains 155k rows... The results can change every minute. Do I need to employ some other tool for my needs?

    I am developing using PHP and mySQL.

    Any and all help would be greatly appreciated

  4. #4
    Join Date
    Oct 2009
    Posts
    658
    You might want to utilize full text search capabilities of database engines. Also you might want to consider adding additional column for your html, css, web developer, etc . . . . That is if it is feasible.

  5. #5
    Join Date
    May 2008
    Location
    Nebraska
    Posts
    32
    resolved - I simply added a column combining the values of the title and description, then cleaned unneccessary words (if, and, then, etc..), then queried the single keyword Column...It now queries in .03 seconds AWESOME!

  6. #6
    Join Date
    Jul 2005
    Location
    USA
    Posts
    912
    It's called a fulltext index column.

    http://dev.mysql.com/doc/refman/5.0/...xt-search.html

    You just repeated a built-in feature in a less than glamorous way. Read the docs before cowboy coding your way to a solution.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles