SQL vs Search Engine

SQL Database (MySQL)

-- Full-text search in MySQL
SELECT * FROM catalog_product_entity
WHERE MATCH(name, description) AGAINST ('wireless headphones' IN BOOLEAN MODE);

-- Filtered search
SELECT p.*, pv.value AS name
FROM catalog_product_entity p
JOIN catalog_product_entity_varchar pv ON p.entity_id = pv.entity_id
WHERE pv.attribute_id = 71
  AND pv.value LIKE '%wireless%'
  AND p.entity_id IN (
    SELECT entity_id FROM catalog_product_entity_int
    WHERE attribute_id = 78 AND value > 0
  );

-- Complex query with joins
SELECT p.*, cpev.value AS name, cpei.value AS qty
FROM catalog_product_entity p
JOIN catalog_product_entity_varchar cpev ON p.entity_id = cpev.entity_id
JOIN catalog_product_entity_int cpei ON p.entity_id = cpei.entity_id
WHERE cpev.attribute_id = 71
  AND cpei.attribute_id = 78
  AND cpei.value > 0
ORDER BY cpev.value ASC
LIMIT 20;

Search Engine (Elasticsearch)

// Elasticsearch query
{
  "query": {
    "bool": {
      "must": [
        { "match": { "name": "wireless headphones" } },
        { "range": { "qty": { "gt": 0 } } }
      ],
      "filter": [
        { "term": { "status": 1 } }
      ]
    }
  },
  "sort": [{ "name": { "order": "asc" } }],
  "size": 20
}

Response Time Comparison

Indexing Strategy

// MySQL indexing
CREATE INDEX idx_product_name ON catalog_product_entity_varchar(value);
CREATE INDEX idx_product_qty ON catalog_product_entity_int(value);
CREATE INDEX idx_product_sku ON catalog_product_entity(sku);

// Elasticsearch indexing
{
  "mappings": {
    "properties": {
      "name": { "type": "text", "analyzer": "standard" },
      "sku": { "type": "keyword" },
      "qty": { "type": "integer" },
      "price": { "type": "float" },
      "categories": { "type": "keyword" },
      "description": { "type": "text", "analyzer": "html_strip" }
    }
  }
}

Resource Usage

MySQL:
├── CPU: High for complex queries
├── Memory: Buffer pool for caching
├── Disk: I/O bound for large datasets
└── Network: Multiple round trips

Elasticsearch:
├── CPU: Inverted index lookups
├── Memory: Field data cache
├── Disk: Segment-based storage
└── Network: Single query response

Use SQL When:

// 1. Exact data retrieval
$product = $this->db->fetchRow(
    'SELECT * FROM catalog_product_entity WHERE sku = ?',
    [$sku]
);

// 2. Complex joins and aggregations
$report = $this->db->fetchAll(
    'SELECT category_id, COUNT(*) as count, AVG(price) as avg_price '
    . 'FROM catalog_product JOIN prices ON ... GROUP BY category_id'
);

// 3. Transactional operations
$this->db->beginTransaction();
$this->db->update('inventory', ['qty' => $newQty], 'product_id = ?', [$id]);
$this->db->insert('inventory_log', [...]);
$this->db->commit();

// 4. Reporting and analytics
$analytics = $this->db->fetchAll(
    'SELECT DATE(created_at) as date, SUM(total) as revenue '
    . 'FROM sales_order GROUP BY DATE(created_at)'
);

Use Search Engine When:

// 1. Full-text search with relevance
$query = new \Elasticsearch\DSL\Query\MatchQuery('name', 'wireless headphones');
$results = $this->searchClient->search(['query' => $query]);

// 2. Faceted search
$agg = new \Elasticsearch\DSL\Aggregation\TermsAggregation('categories');
$results = $this->searchClient->search([
    'aggs' => ['categories' => ['terms' => ['field' => 'categories']]]
]);

// 3. Autocomplete
$query = new \Elasticsearch\DSL\Query\MatchPhrasePrefixQuery('name', 'wire');
$results = $this->searchClient->search([
    'query' => $query,
    'size' => 5
]);

// 4. Complex filtering with performance
{
  "query": {
    "bool": {
      "must": [{ "match": { "name": "phone" } }],
      "filter": [
        { "range": { "price": { "gte": 100, "lte": 500 } } },
        { "term": { "in_stock": true } }
      ]
    }
  }
}

Data Flow

Product Save:
├── 1. Save to MySQL (source of truth)
├── 2. Index to Elasticsearch
└── 3. Update cache

Product Read (search):
├── 1. Query Elasticsearch (fast)
├── 2. Get product IDs
└── 3. Load details from MySQL/Cache

Product Read (direct):
├── 1. Check Redis cache
├── 2. Query MySQL
└── 3. Update cache

Implementation

// Product repository with hybrid approach
class ProductRepository
{
    public function search($query)
    {
        // Search in Elasticsearch (fast)
        $searchResults = $this->searchClient->search([
            'query' => ['match' => ['name' => $query]],
            'size' => 20
        ]);
        
        $productIds = array_column($searchResults['hits']['hits'], '_id');
        
        // Load full data from MySQL
        $products = $this->productCollection->create()
            ->addFieldToFilter('entity_id', ['in' => $productIds])
            ->load();
        
        return $products;
    }
    
    public function getById($id)
    {
        // Direct access - MySQL/Cache
        $cached = $this->cache->load('product_' . $id);
        if ($cached) return unserialize($cached);
        
        $product = $this->resourceModel->load($id);
        $this->cache->save(serialize($product), 'product_' . $id);
        return $product;
    }
}

Consistency

// Index synchronization
public function saveProduct($product)
{
    // Save to MySQL
    $this->resourceModel->save($product);
    
    // Index to Elasticsearch
    $this->searchClient->index([
        'index' => 'catalog_product',
        'id' => $product->getId(),
        'body' => $product->toArray()
    ]);
    
    // Invalidate cache
    $this->cache->remove('product_' . $product->getId());
}

// Handle sync failures
public function saveProductWithRetry($product)
{
    $this->resourceModel->save($product);
    
    try {
        $this->searchClient->index([...]);
    } catch (\Exception $e) {
        $this->logger->error('Index failed: ' . $e->getMessage());
        $this->queue->sendMessage('product.reindex', ['id' => $product->getId()]);
    }
}