1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Searching 3,807,533 rows - php

Discussion in 'PHP & Perl' started by bixfox, Oct 3, 2014.

  1. bixfox

    bixfox Registered Member

    Joined:
    Apr 3, 2014
    Messages:
    65
    Likes Received:
    6
    Occupation:
    BlackHat Stuff
    Hello,
    I'm doing this to search a db with 3,807,533 rows that's over 2.8GB in size


    SELECT * FROM `titles` WHERE (title LIKE '%$search%') ORDER BY `id` DESC LIMIT 30

    when doing so it takes about 20 seconds to search

    server specs:

    duel core Xenon 2.8Ghz
    2GB ddr3 ram


    how can I speed this way up?

    Thanks,
    Bix
     
  2. SharkServers

    SharkServers Jr. VIP Jr. VIP

    Joined:
    Jun 29, 2014
    Messages:
    419
    Likes Received:
    194
    Occupation:
    Web Hosting
    Location:
    DMCA? Pff! www.SuckMyBallsDM.CA
    Home Page:
    • Thanks Thanks x 1
  3. mypmmail

    mypmmail Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    114
    Likes Received:
    27
    What you will need to do if you are using MySQL is to change the data type for 'title' column to text and then use full text search instead of the 'like' clause

    The reason is the 'like' does not use index and is doing a full table scan, thus, you need to replace it with full text search that uses a full text index.

    For other database, the principle is the same, use full text, just that it's being used differently.

    Hope this helps.
     
    • Thanks Thanks x 1
  4. member8200

    member8200 Regular Member

    Joined:
    Aug 9, 2014
    Messages:
    475
    Likes Received:
    33