Re: Find between a list of values

On 03/31/18 10:42, gandalf.corvotempesta_at_gmail.com wrote:
> Let's assume something like this:
>
> start1,stop1|start2,stop2|start3,stop3

For a single column with the data, there is no way for SQL to select out the data where a value is between a start-stop values stored as a string. Index will not help as that is for string search, which ain't what you are trying to do.

If you don't go for the right way of doing, then you need to fetch ALL rows from the database and then parse the start/stop data, and then make a check if the value is between the start/stop range, so for each row of data, the whole check will take longer and longer time, the more start/stop times each row has the more time the script/application has to spend on checking if the value is in the range.

> I would like to select all rows having a defined value (in example "1325" between one of the each group, in other words:
>
> WHERE
> (
> ( 1325 BETWEEN start1 AND stop1 )
> OR ( 1325 BETWEEN start2 AND stop2 )
> OR ( 1325 BETWEEN start3 AND stop2 )
> )

You need to modify your database and store the values in a table with proper columns like:

create table yourtable (

	id ...,
	start ...,
	stop ...,
	iteration int not null

Then you can do a search like

select id from yourtable where 1325 between start and stop

We don't create columns for start1/stop1, start2/stop2 and so on as OR is really slow in SQL, slower than IN, so you want to avoid that. This table gives you the benefit that there ain't a limit on how many different stop start values you have for an id.

Sure you should add index over start and stop column with a create index. See https://dev.mysql.com/doc/refman/5.7/en/create-index.html

You will need to redesign your database and of course the application that fills it with data, so you can use relations to handle things in the proper way instead of stuffing everything into a column.

> Is this possible, with a single query using indexes ? It should be pretty fast.

The fastest you can do with this bad database design is

select * from yourtable where start_stop_column like '1325%'

as that is the only time there is use of the index of the column.

-- 

 //Aho