Professional Web Applications Themes

Query optimization or database structure - MySQL

Hello, I have database with one table: CREATE TABLE `artikel` ( `title` varchar(150) NOT NULL, `description_short` varchar(250) NOT NULL, KEY `title` (`title`), FULLTEXT KEY `title_2` (`title`) ) ENGINE=MyISAM AUTO_INCREMENT=2113423 DEFAULT CHT=latin1 ; MySQL: 5.0.24a-log Server: Opteron 146, RAM 1 GB This table is use to keep information about products. There are about 5 million products in database. The database size is about 5 GB. search_string = it could be few words but rather no more then 5-6 minimum string length is: 3 To find a product in database I use the following query: "SELECT title, MATCH (title) AGAINST ('+$search_string' in ...

  1. #1

    Default Query optimization or database structure

    Hello,
    I have database with one table:

    CREATE TABLE `artikel` (
    `title` varchar(150) NOT NULL,
    `description_short` varchar(250) NOT NULL,
    KEY `title` (`title`),
    FULLTEXT KEY `title_2` (`title`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2113423 DEFAULT CHT=latin1 ;

    MySQL: 5.0.24a-log
    Server: Opteron 146, RAM 1 GB

    This table is use to keep information about products. There are about 5
    million products in database.
    The database size is about 5 GB.

    search_string = it could be few words but rather no more then 5-6
    minimum string length is: 3

    To find a product in database I use the following query:
    "SELECT title, MATCH (title) AGAINST ('+$search_string' in boolean
    mode) as score from artikel where match (title) against
    ('+$search_string*' in boolean mode) order by score";

    It there is some way to optimization query, database structure to get
    result faster.
    Thank you for your help,
    Adam
    Adam Guest

  2. #2

    Default Re: Query optimization or database structure

     

    Check http://tinyurl.com/hwrwb .



    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: Query optimization or database structure

    Radoulov, Dimitre wrote: 
    >
    > Check http://tinyurl.com/hwrwb .
    >
    >
    >
    > Regards
    > Dimitre
    >
    >[/ref]
    Thank you.
    Sphinx seems to be really interesting. Do you know where can I find some
    tutorials how to build search.php file for sphinx. I was searching
    google and only found one on vbulleting forum, but is really complicated.
    Thank you,
    Adam
    Adam Guest

  4. #4

    Default Re: Query optimization or database structure

    [...] 
    >>
    >> Check http://tinyurl.com/hwrwb .
    >>
    >>
    >>
    >> Regards
    >> Dimitre[/ref]
    > Thank you.
    > Sphinx seems to be really interesting. Do you know where can I find some
    > tutorials how to build search.php file for sphinx. I was searching google
    > and only found one on vbulleting forum, but is really complicated.[/ref]

    Did you check the product doentation?

    http://sphinxsearch.com/docse.html


    Regards
    Dimitre


    Radoulov, Guest

  5. #5

    Default Re: Query optimization or database structure

    CUT 

    Thank you again for help. I used perl api to find out all options.
    Sphinx is really amazing (all searching below 0.1 sec) tool if you want
    to use full text searches.
    Adam
    Adam Guest

Similar Threads

  1. need help with query optimization
    By Bosconian in forum MySQL
    Replies: 8
    Last Post: October 10th, 10:30 PM
  2. Assistance with Query Optimization
    By GS in forum MySQL
    Replies: 4
    Last Post: September 13th, 09:27 PM
  3. Query Optimization ... Is this all I can get??
    By Anthony in forum Informix
    Replies: 7
    Last Post: January 16th, 07:51 PM
  4. table space impact on query optimization
    By xixi in forum IBM DB2
    Replies: 2
    Last Post: August 6th, 04:16 PM
  5. Replies: 3
    Last Post: July 11th, 09:08 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139