optimize mysql database search with like operator in column contains mobile numbers - Phpmyadmin

ahmedshaheen
June 8, 2022
130 views
0 votes
2 Answers

I have a table with a column that contains mobile numbers, this table has about 40 millions records.
this is the table schema with some dumping data query

-- phpMyAdmin SQL Dump
-- version 4.9.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jun 08, 2022 at 12:19 PM
-- Server version: 5.7.38-log
-- PHP Version: 7.4.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `database_name`
--

-- --------------------------------------------------------

--
-- Table structure for table `profiles`
--

CREATE TABLE `profiles` (
  `profile_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birth_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `profile_link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `full_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `facebook_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marital_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `profiles`
--

INSERT INTO `profiles` (`profile_id`, `mobile`, `birth_date`, `first_name`, `last_name`, `gender`, `profile_link`, `full_name`, `employer`, `job`, `city`, `facebook_email`, `marital_status`) VALUES
('100000000000529', '201226063866', NULL, 'Samer', 'Aly', 'male', 'https://www.facebook.com/samer.Hussein.aly', 'Samer Hussein Aly', NULL, NULL, 'Mansoura', '[email protected]', NULL),
('100000000004160', '201002299054', NULL, 'Sherif', 'Mansour', 'male', 'https://www.facebook.com/sherif.mansour.946', 'Sherif Mansour', NULL, NULL, NULL, '[email protected]', 'Married'),
('100000000005840', '201000914451', NULL, 'Moniem', 'Allaban', 'male', 'https://www.facebook.com/moniemvilla', 'Moniem Ayman Allaban', 'Civil Engineers', NULL, 'Sohag', '[email protected]', 'Single'),
('100000000006051', '201009975151', NULL, 'Adnan', 'Tahawy', 'male', 'https://www.facebook.com/adnan.tahawy', 'Adnan El Tahawy', 'Allianz Egypt -Head Office', 'Bancasurance', NULL, '[email protected]', NULL),
('100000000007938', '201282553531', '11/21/1991', 'Alaa', 'Horira', 'male', 'https://www.facebook.com/alaa.logo', 'Alaa Abo Horira', 'شركة الدسوقي جروب لأمهات الدواجن', 'Poultry Production Engineer', 'Giza', '[email protected]', 'Single'),
('100000000010755', '201020222026', NULL, 'Ola', 'Abd El-Kader', 'female', 'https://www.facebook.com/ola.eldeeb', 'Ola Abd El-Kader', NULL, NULL, NULL, '[email protected]', NULL),
('100000000014493', '201001041238', '03/09', 'Sherif', 'Ibrahim', 'male', 'https://www.facebook.com/sherifi', 'Sherif Ibrahim', 'The Arab Contractors (Osman Ahmed Osman & Co)', 'Civil engineer', 'Cairo  Egypt', '[email protected]', NULL),
('100000000023706', '201061016412', NULL, 'Ahmed', 'Ramadan', 'male', 'https://www.facebook.com/ahmed.rando', 'Ahmed Ramadan', 'Dubai Islamic Bank', NULL, 'Ma`Adi  Al Qahirah  Egypt', '[email protected]', NULL),
('100000000026495', '201009909238', '10/01/1993', 'Mohamed', 'Fathy', 'male', 'https://www.facebook.com/mido.strong.mohamed', 'Mohamed Fathy', NULL, NULL, 'Giza', '[email protected]', NULL),
('100000000028259', '201112752668', NULL, 'Saher', 'Galal', 'male', 'https://www.facebook.com/SaherrGalall', 'Saher Galal', 'Nothing', NULL, 'Maddi', '[email protected]', NULL),
('100000000034989', '201225550201', NULL, 'Hytham', 'Omar', 'male', 'https://www.facebook.com/hytham.omar', 'Hytham Omar', NULL, NULL, NULL, '[email protected]', 'Married'),
('100000000036425', '201120615833', NULL, 'Youssef', 'Ahmed', 'male', 'https://www.facebook.com/hamada.mody.58910', 'Youssef Ahmed', NULL, NULL, NULL, '[email protected]', NULL),
('100000000046485', '201111755011', NULL, 'Dalia', 'El-Shamy', 'female', 'https://www.facebook.com/AMROKAMAL99', 'Dalia El-Shamy', 'Civil Engineering', NULL, 'Helwan', '[email protected]', 'Married'),
('100000000048402', '201099407065', NULL, 'Waseem', 'Wafeek', 'male', 'https://www.facebook.com/waseem.wafeek', 'Waseem Wafeek', NULL, NULL, NULL, '[email protected]', NULL),
('100000000051463', '201002633598', NULL, 'Sabry', 'Nasrala', 'male', 'https://www.facebook.com/sabry.nasrala', 'Sabry Nasrala', 'Diet House', 'Restaurant manager', 'Cairo  Egypt', '[email protected]', 'Single'),
('100000000053198', '201282832447', '11/14/1968', 'Hatem', 'Moamen', 'male', 'https://www.facebook.com/hatem.moamen', 'Hatem Moamen', 'AOI', 'محاسب', NULL, '[email protected]', 'Married'),
('100000000053761', '201282591776', NULL, 'Bisho', 'Ehab', NULL, 'https://www.facebook.com/bisho.ehab.7', 'Bisho Ehab', NULL, NULL, 'Cairo  Egypt', '[email protected]', NULL),
('100000000053907', '201111621168', NULL, 'Mamduh', 'Zakaria', 'male', 'https://www.facebook.com/abofama', 'Mamduh Mohamed Zakaria', NULL, NULL, NULL, '[email protected]', NULL),
('100000000054846', '201122930402', NULL, 'Manar', 'Boulad', 'male', 'https://www.facebook.com/manar.boulad.7', 'Manar Boulad', 'Damascus  Syria', NULL, 'Damascus  Syria', '[email protected]', NULL),
('100000000056063', '201277682638', NULL, 'Kholoud', 'Osama', 'female', 'https://www.facebook.com/kholoud.osama.31', 'Kholoud Osama', NULL, NULL, NULL, '[email protected]', NULL),
('100000000058700', '201272298772', NULL, 'Magdi', 'Talat', 'male', 'https://www.facebook.com/magdi.talat', 'Magdi Talat', NULL, NULL, 'Minya  Egypt', '[email protected]', 'Married'),
('100000000059048', '201288847174', NULL, 'د. محمد', 'أبوحسين', NULL, 'https://www.facebook.com/100000000059048', 'د. محمد أبوحسين', 'الجهاز المركزي للتنظيم والإدارة', NULL, 'El-Mahmudiya  Al Buhayrah  Egypt', '[email protected]', NULL),
('100000000059243', '201004700599', NULL, 'Ashraf', 'Fathy', 'male', 'https://www.facebook.com/ossoris', 'Ashraf Fathy', NULL, NULL, NULL, '[email protected]', NULL),
('100000000061207', '201002166661', NULL, 'Aisha', 'Abd-Elkader', NULL, 'https://www.facebook.com/toka.mesho', 'Aisha Abd-Elkader', NULL, NULL, NULL, '[email protected]', NULL),
('100000000061245', '201119808229', NULL, 'Sara', 'Mohamed', 'male', 'https://www.facebook.com/ahmed.zohdi.3', 'Sara Mohamed', 'farrahtel', NULL, NULL, '[email protected]', NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `profiles`
--
ALTER TABLE `profiles`
  ADD PRIMARY KEY (`profile_id`),
  ADD KEY `profiles_mobile_index` (`mobile`);
COMMIT;

I want to search in the table for records like that

SELECT profiles.*, coalesce(profiles.mobile, search.mobile) as mobile
from (
        select '2011123123' as mobile
        union all 
            select  '20115434345'
        union all 
            select  '20143534535'
        union all 
            select  '1152840065'
        union all 
            select  '+201152840066'
        union all 
            select  '01152840067'
        union all 
            select  '201152840069'
    ) 
    search
    left join profiles on profiles.mobile like concat('%', search.mobile, '%')

i’m using this query like that because
-first need not to loose not found numbers in search result, i want the not found number to come back in the result but with null in all other columns than mobile that’s why i used left join with search table that is constructed in runtime with only one column which is the mobile numbers i want to search
-second i used like operator in join clause because some numbers may contain the country code which is 20 for example and others may contain it like that +20 and some others may not contain the country code at all like starting with 011 directly for example, that’s why i need the like operator with two wildcards before and after

my question is how to optimize the database to do this query?
will fulltext index in mobile column help ? knowing that the content of mobile column is always one block string i mean like that "0313131231" and not like a paragraph which may has string consists of some words (don’t know if fulltext index is character based or word based)
and thank you in advance and sorry for my English language level

Answers

- ysth
- June 8, 2022 at 7:34 pm
- 0 votes
0
You can use an index on a virtual column containing the phone number reversed:
```
alter table profiles add reverse_mobile varchar(255) as (reverse(mobile)),
    add index reverse_mobile_index (reverse_mobile);

select ...
left join profiles on profiles.reverse_mobile like concat(reverse(search.mobile),'%')
```
fiddle

Note that your search will not find numbers with a + since that is not in the data.
Login or Signup to reply.

- RickJames
- June 20, 2022 at 7:41 am
- 0 votes
0
Cleanse the data and search, if at all possible. It will allow many more optimization options.

If +20 is the only prefix, get rid of it — both in the data and the search. Then the query can be an exact match and you can use IN instead of UNION.

If it is not always +20, then can you discover the country code and remove it? What should be done if the country code is missing?

Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

optimize mysql database search with like operator in column contains mobile numbers – Phpmyadmin

Answers