Results 1 to 13 of 13

Thread: Search Query

  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Search Query

    Hello all,

    I have a problem in building the query for search.

    I have a table which contains a column X stores numerous values in each respective cell of a row.

    For e.g.

    HTML Code:
         
        rows                   COLUMN X
        1                        120;132;143;154;165
        2                        122;134;543;566;
    What I want to do is to make a query which search with a given range and return the rows which falls into the search

    For e.g.

    If I want to get the rows which contains values between 120 and 136 then it should return rows 1 and 2.

    The datatype for Column X is varchar since each cell has to store numerous integers.




    Please help me to get over this.


    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you are asking for is always going to be slow, slow, slow -- it will require a table scan

    do a search for first normal form, and then do whatever you can to change the design of your tables so that each column contains only one value

    you will thank me later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    Hi,

    Thanks for the reply

    But I want it in this manner, sorry.


    Is there a way to achieve it (may be using REGEXP) or is it possible to store a list of integers in a cell?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    REGEXP isn't going to help

    if this design is so great, how come you find it so difficult to write a query to get what you want?

    trust me, you will save yourself a ~lot~ of trouble by simply redesigning the table to conform to first normal form -- only one value any column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Hi,

    Thanks,

    The problem is, there will be 100's of values as such that has to be stored in a row (separated by "," or ";" or any), I have just posted a sample and basic one. I don't think it is a wise idea to make 100's of columns and not sure how many are useful at each time of the data entry (the data might be more columns or may be less).

    Obviously, one value in one column can solve the problem much more easy way. But the basic structure of the data is a crucial here.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by asd1234 View Post
    I don't think it is a wise idea to make 100's of columns
    you obviously still have not bothered to do any research on first normal form

    if you had done so, you would have discovered that you do not need "100's of columns"

    all good articles about first normal form also include hints about how to normalize a structure such as yours with repeating data values

    change this design --
    Code:
    id   columnX
     1   120;132;143;154;165
     2   122;134;543;566
    to this --
    Code:
    id   columnY
     1   120
     1   132
     1   143
     1   154
     1   165
     2   122
     2   134
     2   543
     2   566
    see? not "100's of columns" -- just one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    Thank you very much , That is one good way of doing it.

    But again it will be t0o many rows. How many rows a table can handle?

    Is it possible that it can handle 200 million rows?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it is possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2011
    Posts
    5
    Thank you very much... you saved my day .

  10. #10
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14

    What about load on server?

    Quote Originally Posted by asd1234 View Post
    Thank you very much , That is one good way of doing it.

    But again it will be t0o many rows. How many rows a table can handle?

    Is it possible that it can handle 200 million rows?
    It is possible to store so many rows. But again the increase in number of rows will load server heavily. Isn't it?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vibhavram View Post
    But again the increase in number of rows will load server heavily. Isn't it?
    no, it isn't, not if you have appropriate indexes on your tables that optimize the queries that you need to run

    note that the performance of data sitting in the database is 100%

    in other words, it isn't the number of rows that causes performance problems, what really matters is whether a particular query can be properly optimized

    because if you don't run any query, there is 0% server load
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    no, it isn't, not if you have appropriate indexes on your tables that optimize the queries that you need to run

    note that the performance of data sitting in the database is 100%

    in other words, it isn't the number of rows that causes performance problems, what really matters is whether a particular query can be properly optimized

    because if you don't run any query, there is 0% server load
    Can you suggest me a best online tutorial on database indexing?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are many excellent indexing tutorials, just google and you will find them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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