storing the data

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Archanak
    New Member
    • Sep 2006
    • 79

    #1

    storing the data

    Hi,

    I have 13.8 GB of data.

    The data is of the following:-

    1: Proc Natl Acad Sci U S A. 2003 Aug 19;100(17):9768-73. Epub 2003 Jul 30.

    Characterizatio n of myotubularin-related protein 7 and its binding partner,
    myotubularin-related protein 9.

    Mochizuki Y, Majerus PW.

    Washington University School of Medicine, Department of Internal Medicine, 660
    S. Euclid Avenue, St. Louis, MO 63110, USA.

    Myotubularin-related protein 7 (MTMR7) is a member of the myotubularin (MTM)
    family. The cDNA encoding the mouse MTMR7 contains 1,983 bp, and the predicted
    protein has a deduced molecular mass of 75.6 kDa. Northern and Western blot
    analyses showed that MTMR7 is expressed mainly in brain and mouse neuroblastoma
    N1E-115 cells. Recombinant MTMR7 dephosphorylate d the D-3 position of
    phosphatidylino sitol 3-phosphate and inositol 1,3-bisphosphate [Ins(1,3)P2]. The
    substrate specificity of MTMR7 is different than other MTM proteins in that this
    enzyme prefers the water-soluble substrate. Immunofluoresce nce showed that MTMR7
    is localized in Golgi-like granules and cytosol, and subcellular fractionation
    showed both cytoplasmic and membrane localization of MTMR7 in N1E-115 cells. An
    MTMR7-binding protein was found in an anti-MTMR7 immunoprecipita te from N1E-115
    cells and identified as MTM-related protein 9 (MTMR9) by tandem mass
    spectrometry. The coiled-coil domain of MTMR9 was sufficient for binding to
    MTMR7. The binding of MTMR9 increased the Ins(1,3)P2 phosphatase activity of
    MTMR7. Our results show that MTMR7 forms a complex with MTMR9 and
    dephosphorylate s phosphatidylino sitol 3-phosphate and Ins(1,3)P2 in neuronal
    cells.

    Publication Types:
    In Vitro
    Research Support, U.S. Gov't, P.H.S.

    PMID: 12890864 [PubMed - indexed for MEDLINE]


    I have stored in files.

    I want to store 13.8 GB of data in mysql database.

    Is it reliable to store the data in database or in files?

    The main concept here is user will specify some keyword say "cancer" it should

    retrieve the data which contains the word cancer.(like mentioned above data will be retrieved).

    will searching be faster if store in files or in database?

    with regards
    Archana
  • heat84
    New Member
    • Nov 2007
    • 118

    #2
    There is a trade off . Random access files may be faster but also remember that mySQL uses InnoDB and myISAM for search so you can also store the data in a database. The idea is avoiding sequential access as much as possible

    Comment

    • pbmods
      Recognized Expert Expert
      • Apr 2007
      • 5821

      #3
      Heya, Archana.

      MySQL is very good at this.

      Comment

      • Archanak
        New Member
        • Sep 2006
        • 79

        #4
        Originally posted by pbmods

        Hi,

        Thanks for the link.

        I went through the links.

        I checked in my mysql database but it is giving an error.

        error is this:-

        ERROR 1064: You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1

        I checked the mysql version and version is 3.23.58.

        does full text searches will not work in mysql 3.23.58?

        Is there any other way to be done?

        since i have to use boolean expressions (for eg:-cancer and tissue) it should

        retrieve the articles which has both these words i think i have to use boolean

        mode.But that is giving an error.

        what is compatible to mysql version 3.23.58.

        How should i solve this problem?

        with regards
        Archana

        Comment

        • pbmods
          Recognized Expert Expert
          • Apr 2007
          • 5821

          #5
          Heya, Archana.

          MySQL 3.23.58 should be compatible with fulltext searching. I would strongly recommend that you upgrade (MySQL 3.23.58 was released over four years ago, with two major version releases since then). But if that's not an option, you should be able to get it working with a very minimal feature set.

          What's the query look like?

          Comment

          • Archanak
            New Member
            • Sep 2006
            • 79

            #6
            Originally posted by pbmods
            Heya, Archana.

            MySQL 3.23.58 should be compatible with fulltext searching. I would strongly recommend that you upgrade (MySQL 3.23.58 was released over four years ago, with two major version releases since then). But if that's not an option, you should be able to get it working with a very minimal feature set.

            What's the query look like?

            Hi,
            The query which i have is:-

            just i tried the example that was given.

            [CODE=mysql]CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),bo dy TEXT,FULLTEXT (title,body));[/CODE]

            query OK..

            [CODE=mysql]INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimiz ing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL Security','When configured properly, MySQL ...');[/CODE]

            it is inserting into the table.

            If i give:-

            Code:
            mysql> select * from articles;                                                                                               +----+-----------------------+-------------------------------------+                                                         | id | title                 | body                                |
            +----+-----------------------+-------------------------------------+
            |  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
            |  2 | How To Use MySQL Well | After you went through a ...        |
            |  3 | Optimizing MySQL      | In this tutorial we will show ...   |
            |  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
            |  5 | MySQL Security        | When configured properly, MySQL ... |
            +----+-----------------------+-------------------------------------+
            5 rows in set (0.01 sec)
            [CODE=mysql]SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); [/CODE]

            ERROR 1064: You have an error in your SQL syntax near 'NATURAL LANGUAGE MODE)' at line 1


            It should retrieve the sentences where the database word is present.

            I just tried with single word database to search and retrieve.

            but it is giving an error.

            usually my search string will be like this word1 AND word2 OR word3....

            how do i proceed?

            with regards
            Archana
            Last edited by mwasif; Nov 15 '07, 01:43 PM. Reason: Added code tags.

            Comment

            • pbmods
              Recognized Expert Expert
              • Apr 2007
              • 5821

              #7
              Heya, Archana.

              'IN NATURAL LANGUAGE MODE' is a feature specific to MySQL 5.1. Try removing it.

              Here's a link to the fulltext reference for MySQL 3/4.

              Note that boolean searching was not implemented until MySQL 4.

              Comment

              • mwasif
                Recognized Expert Contributor
                • Jul 2006
                • 802

                #8
                Archanak kindly use proper CODE tags when posting. This makes the post more readable.

                Comment

                • Archanak
                  New Member
                  • Sep 2006
                  • 79

                  #9
                  Originally posted by pbmods
                  Heya, Archana.

                  'IN NATURAL LANGUAGE MODE' is a feature specific to MySQL 5.1. Try removing it.

                  Here's a link to the fulltext reference for MySQL 3/4.

                  Note that boolean searching was not implemented until MySQL 4.
                  Hi,

                  I tried doing but still it is giving error.

                  My query was this:-

                  Code:
                  SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL' IN BOOLEAN MODE);
                  But i got an error..

                  ERROR 1064: You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1

                  How to solve?

                  version os 3.24

                  Comment

                  • mwasif
                    Recognized Expert Contributor
                    • Jul 2006
                    • 802

                    #10
                    BOOLEAN search was introduced in MySQL 4.0.1. You should use with BOOLEAN MODE.
                    [CODE=mysql]SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');[/CODE]
                    Did you look at pbmods's provided link?

                    Comment

                    • Archanak
                      New Member
                      • Sep 2006
                      • 79

                      #11
                      Originally posted by mwasif
                      BOOLEAN search was introduced in MySQL 4.0.1. You should use with BOOLEAN MODE.
                      [CODE=mysql]SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');[/CODE]
                      Did you look at pbmods's provided link?

                      Hi,

                      I just tried in mysql 5.0.22 but it is giving this error:-

                      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NATURAL LANGUAGE MODE)' at line 1

                      How to check this in the manual?

                      How to proceed?

                      with regards
                      Archana

                      Comment

                      • pbmods
                        Recognized Expert Expert
                        • Apr 2007
                        • 5821

                        #12
                        Heya, Archana.

                        NATURAL LANGUAGE MODE is a feature that was introduced in MySQL 5.1. You can omit this to get the same result in 5.0.

                        Comment

                        • Archanak
                          New Member
                          • Sep 2006
                          • 79

                          #13
                          Originally posted by pbmods
                          Heya, Archana.

                          NATURAL LANGUAGE MODE is a feature that was introduced in MySQL 5.1. You can omit this to get the same result in 5.0.
                          Hi,

                          I went through the manual of 3.23 in that they had given like this:-

                          MATCH (col1,col2,...) AGAINST (expr [search_modifier])

                          search_modifier : { IN BOOLEAN MODE | WITH QUERY EXPANSION }.

                          my query was like this :-

                          Code:
                           SELECT * FROM data WHERE MATCH (title) AGAINST ('database' WITH QUERY EXPANSION);
                          
                          SELECT * FROM data WHERE MATCH (title) AGAINST ('+MySQL' IN BOOLEAN MODE);
                          But both gives same error.

                          You have an error in your SQL syntax near 'WITH QUERY EXPANSION)' at line 1.
                          You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1.

                          But fulltext search is supported by mysql 3.23 .I think boolean mode also i can't use.

                          with this version of mysql 3.23 can i perform searching?

                          with fulltext can i search multiple words with Parenthesized groups with boolean operators?

                          like (A AND (B OR C))?

                          I tried using only match operator :-

                          The rows are :-
                          Code:
                          mysql> select * from data;
                          +-----------------------+
                          | title                 |
                          +-----------------------+
                          | MySQL Tutorial        |
                          | How To Use MySQL Well |
                          +-----------------------+
                          2 rows in set (0.02 sec)
                          
                           SELECT * FROM data WHERE MATCH (title) AGAINST ('Tutorial');
                          The result was EMPTY but the word is present in table.

                          How to go about it for multiple combinations with paranthesis?

                          with regards
                          Archana

                          Comment

                          • pbmods
                            Recognized Expert Expert
                            • Apr 2007
                            • 5821

                            #14
                            Heya, Archana.

                            Fulltext searching really only works when you have a relatively large data set. If you have too few records, you will not get results, even when it looks like you should.

                            Comment

                            • Archanak
                              New Member
                              • Sep 2006
                              • 79

                              #15
                              Originally posted by pbmods
                              Heya, Archana.

                              Fulltext searching really only works when you have a relatively large data set. If you have too few records, you will not get results, even when it looks like you should.
                              Hi,

                              I tried with "like" syntax it works.

                              Now i have one problem.

                              User enters a query term like this :- $query="(word1 AND word2) AND (word3 OR word4)";

                              In mysql i gave like this:-

                              Code:
                              select * from table_name where (name RLIKE '[[:<:]]word1[[:>:]]' AND name RLIKE '[[:<:]]word2[[:>:]]') AND (name RLIKE '[[:<:]]word3[[:>:]]' OR name RLIKE '[[:<:]]word4[[:>:]]');
                              But in perl using $sth3=$dbh->prepare(quer y) i don't know how to form a query like above and give in prepare statement for fetching rows.

                              How to prepare a query statement to take the query terms given by user?

                              How to give in prepare statement like this :-

                              Code:
                              select * from table_name where ()?
                              wht condition should i gave to take query terms?

                              If user gives only one term like "word1"

                              then i can give:-

                              Code:
                              "select * from table_name where name like '%$query%'";
                              It fetches the rows..

                              But for above query combination "(word1 AND word2 ) combination how do i give?


                              I don't know to which forum this question belongs so i taught its more appropriate to Mysql so i posted here?

                              How to solve this?

                              Comment

                              Working...