Mysql - regex acceleration

Question

I have a table:

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| idurl  | int(11)          | NO   | PRI | NULL    | auto_increment |
| idsite | int(10) unsigned | NO   | MUL | NULL    |                |
| url    | varchar(2048)    | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

select statement:

SELECT idurl,
       url
  FROM URL
 WHERE idsite = 34
   AND url REGEXP '^https\\://www\\.domain\\.com/checkout/step_one\\.php.*'

The request takes 5 seconds on the table with 1,000,000 rows. Can I achieve acceleration using indexes or something else?

Answer 1

Instead of a regular expression, you can use the operator LIKE. But since your regex is simple, it may or may not improve performance.

You can split the domain into a separate field, index it and use it in your where clause. If the URLs you store are from different domains, then such an index can significantly improve performance.

Answer 2

, LIKE . LIKE % .

AND url LIKE 'https://www.domain.com/checkout/step_one.php%'

LIKE ^. :

'Sherlock and Watson' LIKE 'and%'
'Sherlock and Watson' LIKE '%and%'
'Sherlock and Watson' LIKE '%and'

Answer 3

, URL, , , - , , .

, , IDSITE , - IDSITE, WHERE IDSITE = 34, URL-.

- :

select
    idurl,
    url
from
    (select idurl, url from uwe_url where idsite = 34)
where
    url REGEXP '^https\\://www\\.domain\\.com/checkout/step_one\\.php.*'

, URL.

Answer 4

, REGEXP.

:

AND eu.url LIKE 'https://www.domain.com/checkout/step_one.php%'