Page 1 of 2 12 LastLast
Results 1 to 25 of 26

Thread: MySQL -

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573

    MySQL -

    .
    ( ).
    !
    2- , , :

    Code:
    SELECT `id` FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva' IN BOOLEAN MODE) = 1
    50 , 2 , . , 0.005.:

    Code:
    SELECT `id` FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva' IN BOOLEAN MODE) >= 1
    5 , 3 ( >= 3...)
    , .

    ps - - ... sql-.. ..
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  2. #2
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    , Fulltext search boolean mode , fulltext index?

    MySQL :
    "They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. "

    - . , - , repair optimize .

    ! - !
    ''? * *
    - !
    ...

  3. #3
    Registered User SAGE's Avatar
    Join Date: Sep:2005
    Location: Sofia
    Posts: 171
    Quote Originally Posted by BornToDrink View Post
    .
    .........................
    , .

    ps - - ... sql-.. ..

    , , 1 (=1), .

    ( )

  4. #4
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    .
    50 , 1 , , , 5 , 49995 .
    , 4000 ( , 2 , 0.0005 )

    .

    - , .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  5. #5
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    "=1" .
    MATCH...AGAINST. , relevance, BOOLEAN MODE, 0 1. 3 , relevance 0, 1, 2 3 , .

    :
    Code:
    SELECT `id`, MATCH(`title`, `body`) 
    AGAINST ('dumanekva' IN BOOLEAN MODE) AS `relevance`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva' IN BOOLEAN MODE)
    ORDER BY `relevance` DESC
    Code:
    SELECT `id`, MATCH(`title`, `body`) 
    AGAINST ('dumanekva') AS `relevance`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva' IN BOOLEAN MODE)
    ORDER BY `relevance` DESC
    ''? * *
    - !
    ...

  6. #6
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    .
    , ID- .
    , , - , .

    :
    Code:
    SELECT `id`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva drugaduma oshteedna' IN BOOLEAN MODE) >= 2
    Query took 0.05 sec ( -, )
    " = 2", ~2 - e .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  7. #7
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    Code:
    FLUSH TABLE `table`;
    SELECT `id`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva drugaduma oshteedna' IN BOOLEAN MODE) >= 2
    AND
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva drugaduma oshteedna' IN BOOLEAN MODE) < 3
    :

    Code:
    FLUSH TABLE `table`;
    SELECT `id`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva drugaduma oshteedna' IN BOOLEAN MODE) = 2;
    :
    Code:
    FLUSH TABLE `table`;
    SELECT `id`
    FROM `table` 
    WHERE MATCH(`title`, `body`) 
    AGAINST ('dumanekva drugaduma oshteedna' IN BOOLEAN MODE) = 2.0;
    .

    , .

    ---------- Post added at 17:34 ---------- Previous post was at 17:28 ----------

    50% - , . .
    ''? * *
    - !
    ...

  8. #8
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    . relevance MATCH...AGAINST, .
    relevance = 1 . relevance - 1, , relevance=0 .
    "=1"? . MySQL , - .

    , -.
    , , :
    Code:
    FLUSH TABLE `tablename`;
    ''? * *
    - !
    ...

  9. #9
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    , , .
    3 2 title/body, "= 2", , ">= 2" . , 5 3, 3, , .
    , ( , ).
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  10. #10
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    1 - 0.0012
    2 - 2.0723
    3 - 2.0639

    2 3 , flush .
    ... ..

    ps - WHERE AND-a.

    Edit:
    -? , ?
    phpmyadmin, flush .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  11. #11
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138

    Cool

    , phpmyadmin-a, , -.

    , localhost.

    :
    Code:
    FLUSH table `articles`;
    SELECT `id`
    FROM `articles` 
    WHERE MATCH(`title`, `description`) 
    AGAINST ('  ' IN BOOLEAN MODE) = 2;
    .

    , fulltext , :
    - 0.0118
    >=2 - 0.0198
    =2 - 0.0232
    =3 - 0.0734
    >=3 - 0.0734

    :
    - 0.0015
    >= - 0.0019
    =2 - 0.0225
    =3 - 0.0738
    >=3 - 0.0040

    , "=" MySQL ! 10 - , "=" .

    ?

    ---------- Post added at 18:12 ---------- Previous post was at 18:06 ----------

    Quote Originally Posted by BornToDrink View Post
    ... ..
    , workaround-, ?

    EDIT:>> - mysql 5.1.30
    ''? * *
    - !
    ...

  12. #12
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    , alter- .
    ( ) , .. .

    , , .
    , 20-30 .
    load- 12, . A CPU usage-a MySQL- 360%. , - 2 "="...
    E, >= ( - like... )
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  13. #13
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    Quote Originally Posted by BornToDrink View Post
    , alter- .
    ( ) , .. .
    CREATE TABLE , , . , .
    Quote Originally Posted by BornToDrink View Post
    , , - .
    10 , 1000 - .
    , . 5 100 -.
    ''? * *
    - !
    ...

  14. #14
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    - , 20 . , "=" MySQL 10 - , , MATCH...AGAINST "<x+1" ">=x".
    ''? * *
    - !
    ...

  15. #15
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    , , - .
    10 , 1000 - .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  16. #16
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    bugs.mysql.com, .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  17. #17
    Registered User
    Join Date: Jul:2005
    Location: Sofiq
    Posts: 2,798
    HAVING ?

    Code:
    SELECT `id`, MATCH(`title`, `description`) 
    AGAINST ('  ' IN BOOLEAN MODE) as `match`
    FROM `articles` 
    WHERE MATCH(`title`, `description`) 
    AGAINST ('  ' IN BOOLEAN MODE)
    HAVING `match` = 2
    EXPLAIN ? ? myisam_sort_buffer_size key_buffer_size?

  18. #18
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    Quote Originally Posted by BornToDrink View Post
    bugs.mysql.com, .
    ? .
    ''? * *
    - !
    ...

  19. #19
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    .
    , .

    .
    score-, 2 , :

    Code:
    SELECT objects.id, objects.title, objects.`body`, regions.name,
    MATCH (objects.title,objects.`body`,regions.name) AGAINST ('   ' IN BOOLEAN MODE) AS score
    FROM objects
    LEFT JOIN regions
    ON objects.region = regions.id
    HAVING score >= 3
    ORDER BY score DESC
    , - >5 .
    , , objects region_ID, ( ). , , ( MATCH()):

    Code:
    SELECT objects.id, objects.title, objects.`body`,
    MATCH (objects.title,objects.`body`,objects.region_name) AGAINST ('   ' IN BOOLEAN MODE) AS score
    FROM objects
    HAVING score >= 3
    ORDER BY score DESC
    , .
    .. .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  20. #20
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    Match , . .

    , :
    .....
    ...
    WHERE objects.region = X

    JOIN.


    P.S. - , .
    ''? * *
    - !
    ...

  21. #21
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    Quote Originally Posted by vbTheKing View Post
    Match , . .
    .
    Quote Originally Posted by vbTheKing View Post
    , :
    .....
    . .. .
    Quote Originally Posted by vbTheKing View Post
    P.S. - , .
    [/QUOTE]
    .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  22. #22
    Registered User
    Join Date: Jul:2005
    Location: Sofiq
    Posts: 2,798
    A http://sphinxsearch.com/. - MyISAM full text search. , 100
    Last edited by _ShadoW_; 12th November 2010 at 11:55.

  23. #23
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    Quote Originally Posted by _ShadoW_ View Post
    A http://sphinxsearch.com/. - MyISAM full text search. , 100
    .
    NoSQL , ( - ) . , . , .
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  24. #24
    hmm BornToDrink's Avatar
    Join Date: Aug:2005
    Location: .
    Posts: 11,573
    .
    Looks like index is not used with =2:
    , .
    http://bugs.mysql.com/bug.php?id=57354
    Hey, mother, I come bearing a gift. I'll give you a hint. It's in my diaper and it's not a toaster.
    .

  25. #25
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    , , -. ...
    ''? * *
    - !
    ...

Posting Permissions

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

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |