SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Sydney
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow mysql Search from php

    i am currently developing a lyrics web site


    there are about 40000 record in the lyrics table



    i preform a query to select result and number of result


    PHP Code:
        $sresult = @mysql_query("SELECT ID, Song_name, Album_name, Aritse_name  
    FROM lyrics 
    Where Song_name like '%
    $search%'  OR Aritse_name like '%$search%' 
    ORDER BY `Hit` DESC limit 
    $offset,$limit");
        
    $ssresult = @mysql_query("SELECT count(ID) FROM lyrics Where Song_name like '%$search%' OR Aritse_name like '%$search%'");
        
    $num_s=mysql_fetch_array($ssresult); 
        
    $numsearch=$num_s[0]; 
    all the field with "order by" and "like" i have index them.
    But unlucky the speed is poor, any one any idea..?
    such as temp table or something else

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you don't need the second query.
    PHP Code:
    $sresult = @mysql_query("SELECT ID, Song_name, Album_name, Aritse_name  
    FROM lyrics 
    Where Song_name like '%
    $search%'  OR Aritse_name like '%$search%' 
    ORDER BY `Hit` DESC limit 
    $offset,$limit");

    $numsearch mysql_num_rows($sresult); 
    Hehe - hang on that won't work because of your limit clause. OK how about:
    Code:
    SELECT ID, Song_name, Album_name, Aritse_name, COUNT(*)
    FROM lyrics
    Where Song_name like '%$search%'
    OR Aritse_name like '%$search%'
    ORDER BY `Hit` DESC
    LIMIT $offset,$limit
    The last column in your result set will contain the COUNT - but you will only need to read it once.
    Last edited by freakysid; Dec 2, 2002 at 00:40.

  3. #3
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Sydney
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks your reply. But.

    thanks your reply.

    it looks more reasonable to combine 2 query into one which speed up the run time for almost 100%
    but i want the query to get the search result and the number of total search result
    PHP Code:

    $sresult 
    = @mysql_query("SELECT ID, Song_name, Album_name, Aritse_name, COUNT( * ) 
    FROM lyrics
    Where Song_name
    like '%
    $search%' OR Aritse_name
    like '%
    $searcg%'
    GROUP BY 'Song_name'
    ORDER BY `Hit` DESC "
    );

    // How to get number of search result from that query?



         
    while ( $row mysql_fetch_array($sresult) ) {

    echo (
    "<href a=?song=".$row["Song_name"].">$row["Song_name"]</a>");
        }
    // end while
       
    mysql_free_result($sresult); 

    // How to get number of search result from that query?
    into $numsearch var

    Thanks

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    North Wales, UK
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might want to look at fulltext indexing in Mysql which should speed up the searching considerably.


Bookmarks

Posting Permissions

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