MySQL Performance – Using VARCHAR with Ranges

MySQL

I will try to describe my problem as simple as I can.

I have a db with tables that contains columns with phone numbers ranges, e.g. numbertype_id = 7, number_start = '00110000', number_end = '00119999', numbers = 10000 (of course I have an id pk and unique key index as well).

On the site that uses those tables, I have a search functionality, which should find a number within those ranges (if there is any). For example, user searches for '00112233', and it should return true, since there is a hit.

Now the problem is performance. I am searching only in one table per search, with a max of under 200k rows.

Being varchar columns (and need to be, since with int I would loose the leading 0's, plus the leading zeros could be none, up to 6-7), for me the obvious choice was a search like this:

SELECT * 
FROM numberdata 
WHERE numbertype_id = 7
  AND number_start <= '00112233'
  AND number_end >= '00112233'

Depending on the table, that could take anywhere between 1.5 to 2.5 seconds, and it's not normal to take that long.

Does someone knows a better solution for that?

IMPORTANT UPDATE

I did not explained one vital detail. No matter that the number searched for returns any result or now, I still have to "offer" 45 alternatives, and have to search for those 45 numbers in part as well. So the total queries are 46 actually.

SHOW CREATE TABLE returns this

CREATE TABLE 'numberdata' (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier',
'numbertype_id' int(10) unsigned NOT NULL DEFAULT '0',
'prefix' varchar(5) NOT NULL DEFAULT '',
'number_start' varchar(20) NOT NULL DEFAULT '',
'number_end' varchar(20) NOT NULL DEFAULT '',
'number_count' int(10) unsigned NOT NULL DEFAULT '0',
'owner' varchar(100) DEFAULT NULL,
'owner_city' varchar(100) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY number ('numbertype_id','number_start','number_end')
) ENGINE=InnoDB AUTO_INCREMENT=48690 DEFAULT CHARSET=utf8

And EXPLAIN SELECT... returns this

id select_type table      type possible_keys key    key_len ref   rows  Extra
1  SIMPLE      numberdata ref  number        number 4       const 17174 Using index condition

  • Is it possible for the query to return 2 or more rows? Or will it always return 1 (or 0) rows?

    For any number I return only one result, or none (in case it's not present in any of the ranges). No possibilities for duplicates.

  • Can we assume that all values in number_start and number_end are left-padded with 0s to the same length?

    Yes, that's correct.

Best Answer

Assumptions:

  • the query can only return 1 row (or none) and never 2 or more, i.e. the (number_start, number_end) ranges are not overlapping for the same numbertype_id.

  • the number_start and number_end values are all left-padded with 0s to the same length.

Under the above assumptions, we can use a trick to use an index to fetch exactly 1 row (if there is one) that matches the criteria (not all, checking only the low end of the range) - and don't search for more rows. The check on the high end is done before the row is returned to avoid any false positive result:

SELECT n.* 
FROM 
    ( SELECT id, number_end
      FROM numberdata 
      WHERE numbertype_id = 7 
        AND number_start <= '00112233'
      ORDER BY number_start DESC
      LIMIT 1
    ) AS ni
  JOIN
    numberdata AS n
  ON  ni.number_end >= '00112233'
  AND ni.id = n.id ;

The query will only need an index on (numbertype_id, number_start) - or keep the one you have on (numbertype_id, number_start, number_end).

Related Question