Schema Discovery

Explore your data structure and available tables using schema discovery commands.

SHOW TABLES

List all available tables (indexes) in your database:

-- Show all tables
SHOW TABLES;

-- Show tables matching a pattern
SHOW TABLES LIKE 'logs%';
SHOW TABLES LIKE '%_metrics';
SHOW TABLES LIKE 'user_*';

Pattern Matching

The LIKE clause supports SQL wildcards:

• % - Matches any sequence of characters
• _ - Matches any single character

-- Examples of pattern matching
SHOW TABLES LIKE 'log%';        -- Tables starting with "log"
SHOW TABLES LIKE '%_data';      -- Tables ending with "_data"  
SHOW TABLES LIKE 'test_%';      -- Tables starting with "test_"
SHOW TABLES LIKE 'user_____';   -- Tables with exactly 9 characters starting with "user_"

Response Format

{
  "columns": [
    {"name": "TABLE_NAME", "type": "string"}
  ],
  "rows": [
    ["logs"],
    ["users"], 
    ["events"],
    ["metrics"]
  ],
  "size": 4
}

DESCRIBE

Get detailed information about a table's structure:

-- Describe a specific table
DESCRIBE logs;

-- Alternative syntax
DESC users;

Response Format

{
  "columns": [
    {"name": "COLUMN_NAME", "type": "string"},
    {"name": "DATA_TYPE", "type": "string"},
    {"name": "SOURCE", "type": "string"}
  ],
  "rows": [
    ["timestamp", "date", "indexed"],
    ["level", "keyword", "indexed"],
    ["message", "text", "analyzed"],
    ["user_id", "keyword", "indexed"],
    ["service", "keyword", "indexed"],
    ["response_time", "integer", "indexed"]
  ],
  "size": 6
}

Data Types

Understanding the data types helps you write effective queries:

Core Data Types

Type	Description	Example Values	Query Usage
text	Full-text searchable	"Error connecting to database"	LIKE operations, text search
keyword	Exact match strings	"error", "api-gateway"	WHERE equality, GROUP BY
integer	Whole numbers	200, 404, 1000	Mathematical operations, ranges
float	Decimal numbers	123.45, 0.95	Mathematical operations, averages
boolean	True/false values	true, false	Boolean logic, filtering
date	Timestamps	"2024-01-15T14:30:00Z"	Time-based queries, DATE functions

Specialized Data Types

Type	Description	Example Values	Query Usage
geo_point	Geographic coordinates	[37.7749, -122.4194]	Geospatial functions
vector	Dense vector embeddings	[0.1, 0.2, 0.3, ...]	Vector similarity search

Practical Examples

Exploring Available Data

-- Step 1: See what tables are available
SHOW TABLES;

-- Step 2: Examine a specific table structure
DESCRIBE logs;

-- Step 3: Find related tables
SHOW TABLES LIKE '%user%';
SHOW TABLES LIKE '%log%';

Understanding Table Relationships

-- Check user-related tables
SHOW TABLES LIKE '%user%';
DESCRIBE users;
DESCRIBE user_sessions;
DESCRIBE user_preferences;

-- Check log-related tables  
SHOW TABLES LIKE '%log%';
DESCRIBE logs;
DESCRIBE error_logs;
DESCRIBE access_logs;

Data Type Analysis

-- After DESCRIBE logs, you might see:
-- timestamp (date) - use for time-based filtering
-- level (keyword) - use for exact matching and grouping
-- message (text) - use for full-text search
-- user_id (keyword) - use for joins and filtering
-- response_time (integer) - use for mathematical operations

-- Based on the schema, write appropriate queries:
SELECT level, COUNT(*) 
FROM logs 
WHERE timestamp >= '2024-01-01'
GROUP BY level;  -- level is keyword type, good for grouping

SELECT * 
FROM logs 
WHERE message LIKE '%error%'  -- message is text type, good for LIKE
  AND response_time > 1000;   -- response_time is integer, good for comparison

Schema-Driven Query Development

1. Discovery Workflow

-- Start with discovery
SHOW TABLES;

-- Focus on relevant tables
SHOW TABLES LIKE '%performance%';
SHOW TABLES LIKE '%metric%';

-- Understand structure
DESCRIBE performance_logs;
DESCRIBE system_metrics;

2. Build Queries Based on Schema

-- If DESCRIBE shows these fields for 'performance_logs':
-- service (keyword), response_time (integer), timestamp (date), user_id (keyword)

-- Write schema-appropriate queries:
SELECT 
  service,                    -- keyword: good for grouping
  AVG(response_time),         -- integer: good for math operations  
  COUNT(*) as request_count
FROM performance_logs
WHERE timestamp >= '2024-01-01'  -- date: good for time filtering
GROUP BY service                 -- keyword: efficient grouping
ORDER BY AVG(response_time) DESC;

3. Join Planning

-- Use DESCRIBE to understand join possibilities
DESCRIBE users;     -- Look for: id (keyword), username (keyword)
DESCRIBE logs;      -- Look for: user_id (keyword), timestamp (date)

-- Plan joins based on compatible field types
SELECT 
  u.username,           -- keyword from users
  COUNT(l.id) as logs
FROM users u
LEFT JOIN logs l ON u.id = l.user_id  -- keyword = keyword (good match)
GROUP BY u.username;

Best Practices

Schema Exploration

1. Start broad, then narrow down:

   SHOW TABLES;                    -- See everything
   SHOW TABLES LIKE '%log%';       -- Focus on log tables
   DESCRIBE application_logs;      -- Deep dive into specific table

2. Understand data types before querying:

   DESCRIBE table_name;
   -- Plan your WHERE clauses, GROUP BY, and JOINs based on field types

3. Use patterns to find related tables:

   SHOW TABLES LIKE '%user%';      -- User-related tables
   SHOW TABLES LIKE '%_metrics';   -- Metrics tables
   SHOW TABLES LIKE 'prod_%';      -- Production tables

Query Optimization

1. Match your operations to data types:

   • Use = and IN for keyword fields
   • Use LIKE for text fields
   • Use mathematical operations for integer/float fields
   • Use date functions for date fields

2. Plan efficient joins:

   • Join on keyword fields when possible
   • Ensure both sides of JOIN have compatible types

3. Leverage specialized types:

   • Use geospatial functions for geo_point fields
   • Use vector similarity for vector fields

Example: Complete Discovery to Query

-- 1. Discovery
SHOW TABLES LIKE '%order%';
-- Result: orders, order_items, order_history

-- 2. Schema analysis  
DESCRIBE orders;
-- Result: id (keyword), user_id (keyword), total (float), created_at (date)

DESCRIBE order_items;  
-- Result: order_id (keyword), product_id (keyword), quantity (integer), price (float)

-- 3. Schema-informed query
SELECT 
  DATE(o.created_at) as order_date,     -- date type: use DATE function
  COUNT(DISTINCT o.id) as order_count,  -- keyword type: good for DISTINCT
  SUM(oi.quantity * oi.price) as revenue -- integer/float: good for math
FROM orders o
JOIN order_items oi ON o.id = oi.order_id  -- keyword = keyword: efficient join
WHERE o.created_at >= '2024-01-01'          -- date type: time-based filtering
GROUP BY DATE(o.created_at)                 -- date type: time-based grouping
ORDER BY order_date DESC;