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.

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

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

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?

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

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.

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 --to this --see? not "100's of columns" -- just one

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?

yes it is possible

Thank you very much... you saved my day .

It is possible to store so many rows. 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

Can you suggest me a best online tutorial on database indexing?

there are many excellent indexing tutorials, just google and you will find them