Useful queries

Useful Queries

Here is a list of useful MySQL queries, for various things.

Comparing databases

Comparing core_config_data table values from within 2 databases.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT prod.*, dev.value
FROM `database1`.core_config_data as prod 
LEFT JOIN `database2`.core_config_data as dev ON 
(
    prod.scope = dev.scope
    AND prod.scope_id = dev.scope_id
    AND prod.path = dev.path
)

WHERE prod.value != dev.value

The one below is exluding store URL's

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT prod.*, dev.value
FROM `database1`.core_config_data as prod 
LEFT JOIN `database2`.core_config_data as dev ON 
(
    prod.scope = dev.scope
    AND prod.scope_id = dev.scope_id
    AND prod.path = dev.path
)

WHERE prod.value != dev.value 
AND prod.path NOT LIKE 'web%'