# Mysql data mapping for Elasticseach

**URL:** <https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239>\
**Category:** Elasticsearch\
**Created:** [February 19, 2016, 12:36pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239 "2016-02-19T12:36:27Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 19, 2016, 12:36pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/1 "2016-02-19T12:36:27Z")

</div>

Hi,  
I am in the process of converting my queries from Mysql to Elasticsearch, and would like to know if my mapping makes sense.

I've identified 4 common types of data that I want to store.

1. `tip` : "tip" is my enum field name in mysql db. For exampe, tip=M, tip=X or tip=D.

2. `usul` : "usul" is my enum field name in mysql db.  
For example, usul=A, usul=P, usul=M or usul=K.

3. `maliyet` : "maliyet" is my char field name in mysql db.  
For example, maliyet=1, maliyet=2, maliyet=X or maliyet=0.

4. `islem` : "islem" is my varchar field name in mysql db.  
For example, islem=Y, islem=YD, islem=YDDD, islem=YI or islem=YSS.

 ![](https://us1.discourse-cdn.com/elastic/original/2X/0/0b44d18a8d1d5d726fa32c862bddf67040be0f31.png)

Mappings:

```
1. tip - {type: string, index:not_analyzed} 

2. usul - {type: string, index:not_analyzed} 

3. maliyet - {type: string, index:not_analyzed} 

4. islem - {type: nested, index:not_analyzed} 

```

However, my serach results is not correct. I want to do mapping. How can I do mapping these fields?

---

<div class="post-metadata">

**Author:** ![ebuildy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ebuildy/32/6070_2.png) [@ebuildy](https://discuss.elastic.co/u/ebuildy)\
**Post date:** [February 19, 2016, 1:10pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/2 "2016-02-19T13:10:46Z")

</div>

Hello, you create the index (like a mysql data-base) first:

[https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-create-index.html#mappings](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-create-index.html#mappings)

With your mapping, then insert documents.

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 19, 2016, 1:19pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/3 "2016-02-19T13:19:41Z")

</div>

I have a index called "ihaleler". My question is this not

I indexed 10000 datas but these fields (`'tip', 'usul', 'maliyet', 'islem'`) mapping is not correct. So my search results is not correct.

What should be the type of these fields for mapping?

```
PUT ihaleler/ihale/_mapping
{
  "properties": {
    "tip" : {
      "type" : "?"
        , "index": "not_analyzed"
      }
  }
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 19, 2016, 3:15pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/4 "2016-02-19T15:15:28Z")

</div>

I think your mapping is correct and using `not_analyzed` is the way to go in such a case.

But it's hard to tell why your query fails without seeing the query.

Remember that `not_analyzed` will not lowercase your term. So if you indexed `YSS`, searching for `yss` won't work.

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 9:07am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/5 "2016-02-29T09:07:38Z")

</div>

Ok. I did "`usul`", `"tip`" and "`maliyet`" fields. But I have a problem about "islem". It differs from others.  
My query :

`http://10.0.2.15:8080/filter.php?islem=YDDD`

My result:

```
found = 2
->YDDD
->YDDD

```

It works true actually but I want to be YD instead of YDDD

Because YDDDDD=YDDDD=YDDD=YD. So it have to be unique array.

Mysql db screenshot:

 ![](https://us1.discourse-cdn.com/elastic/original/2X/4/441fee255b766fd5e46da4076fce5869cd1d14b5.png)

CODES:

"islem" mapping on sense editor :

```
PUT ihaleler/ihale/_mapping
{
  "properties": {
    "islem": {
      "type": "string",
      "index": "analyzed",
      "analyzer": "turkish"
    }
  }
}

```

indexed codes on php (index.php):

```
....
$islem=$ihale['islem'];
            $islem_array = array();
               $islem_array[] = 'Y';
               //$islem=$ihale['islem']; 
               for($i=0;$i<strlen($islem);$i++){
                   $islem_array[]=substr($islem,0,1);
               }
            $islem=array_unique($islem_array);    
   
$dizi['islem']=$ihale['islem'];

```

filter codes on php(filter.php) :

```
 unset($filter_islem);
    if(isset($_GET['islem'])){
    $islemler=$_GET['islem'];
    $islemler=trim($islemler, ',');
    $islemler_array=explode(',', $islemler);
    $islem=array_unique($islemler_array);
    $filter_islem['match_phrase']['islem']=$_GET['islem'];
    }
if(is_array($filter_islem)){
    $searchParams['body']['query']['bool']['should']=$filter_islem;
} 

```

How can I do? Is the mapping must be a nested instead of string?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 29, 2016, 9:21am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/6 "2016-02-29T09:21:35Z")

</div>

If you want to run a query like "starts with", you could use [edge n grams](https://www.elastic.co/guide/en/elasticsearch/reference/2.2/analysis-edgengram-tokenizer.html) for that.

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 9:51am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/7 "2016-02-29T09:51:37Z")

</div>

no it is not.

```
Y: Yeni (English meanings new)
D: Düzeltme (English meanings correction)

Y: Yeni(New)
YD: Yeni Düzeltme(New Correction)
YDD: Yeni Düzeltme Düzeltme(New Correction Correction)
YDDD: Yeni Düzeltme Düzeltme Düzeltme(New Correction Correction Correction) 
...etc.

```

When I query YDDD It must come just YD. (because YD=YDDD)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 29, 2016, 10:05am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/8 "2016-02-29T10:05:22Z")

</div>

Sorry. I don't understand.

Can you reproduce your case with a small example?

- create a new index
- create a type with a mapping and a single field
- index some documents using this single field
- query

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 10:30am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/9 "2016-02-29T10:30:24Z")

</div>

1.I have mysql datase and my field name is islem(english meaning process).  
2.I created index on sense editor:

```
DELETE ihaleler
PUT ihaleler

```

3.I did mapping for my field "islem"

```
PUT ihaleler/ihale/_mapping
{
  "properties": {
    "islem": {
      "type": "string",
      "index": "analyzed",
      "analyzer": "turkish"
    }
  }
}

```

4.I indexed data on php (index.php):  
I made array of process variable in here. After I did unique this array.

```
....
$islem=$ihale['islem'];
            $islem_array = array();
               $islem_array[] = 'Y';
               //$islem=$ihale['islem']; 
               for($i=0;$i<strlen($islem);$i++){
                   $islem_array[]=substr($islem,0,1);
               }
            $islem=array_unique($islem_array);    
$dizi['islem']=$ihale['islem'];

```

5.Filtered indexing data on php(filter.php) :

```
 unset($filter_islem);
    if(isset($_GET['islem'])){
    $islemler=$_GET['islem'];
    $islemler=trim($islemler, ',');
    $islemler_array=explode(',', $islemler);
    $islem=array_unique($islemler_array);
    $filter_islem['match_phrase']['islem']=$_GET['islem'];
    }
if(is_array($filter_islem)){
    $searchParams['body']['query']['bool']['should']=$filter_islem;
}

```

6.And query :

`http://10.0.2.15:8080/filter.php?islem=YDDD`

I want to be YD of this query result .  
Because in the future it could be very long.

For example:  
like YDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [February 29, 2016, 10:43am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/10 "2016-02-29T10:43:32Z")

</div>

Where is the edge n gram in your mapping?

---

<div class="post-metadata">

**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\
**Post date:** [February 29, 2016, 10:47am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/11 "2016-02-29T10:47:12Z")

</div>

I think you can do ?islem=YDD\* when searching.

In stead of using your filter.php, try to verify the search results from ES's REST interface to make sure it works the way you want... then review the filter.php module.

Here is the URL you can try...  
http://[es host:es port]/[index name]/\_search?q=islem:YDD\*

Here is the link for further info  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-uri-request.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-uri-request.html)

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 12:44pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/12 "2016-02-29T12:44:59Z")

</div>

edge n gram :

```
PUT /ihaleler
    {
        "settings" : {
            "analysis" : {
                "analyzer" : {
                    "my_edge_ngram_analyzer" : {
                        "tokenizer" : "my_edge_ngram_tokenizer"
                    }
                },
                "tokenizer" : {
                    "my_edge_ngram_tokenizer" : {
                        "type" : "edgeNGram",
                        "min_gram" : "0",
                        "max_gram" : "1",
                        "token_chars": ["letter"]
                    }
                }
            }
        }
    }
```

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 12:58pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/13 "2016-02-29T12:58:33Z")

</div>

NO thn tri-man.  
You didn't understand me.

when I do query `http://10.0.2.15:8080/filtreleme.php?islem=YDDDDDD`

my result shoulnd't be islem=YDDDDDD .  
It should be islem=YD. Five of the D here is unnecessary

---

<div class="post-metadata">

**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\
**Post date:** [February 29, 2016, 1:12pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/14 "2016-02-29T13:12:33Z")

</div>

Got it now... use the edgeNGram as suggested by @dadoonet is the right approach. I would change "min\_gram" to 1 and "max\_gram" to 2 based on your example (ie. Y, YD, YDD, etc.)

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 1:37pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/15 "2016-02-29T13:37:08Z")

</div>

I did but result is the same.  
My query :

```
http://10.0.2.15:8080/filter.php?islem=YDDD

```

My result:

```
found = 2
->YDDD
->YDDD

```

Actually it should be :

```
    found = 2
    ->YD
    ->YD
```

---

<div class="post-metadata">

**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\
**Post date:** [February 29, 2016, 1:58pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/16 "2016-02-29T13:58:24Z")

</div>

Oh, are you expecting to see the results back as YD, not YDDD (where YDDD is the actual value)?

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [February 29, 2016, 2:04pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/17 "2016-02-29T14:04:54Z")

</div>

yes. My islem datas`Y,D,I,S`

for example :

```
Y
YD
YI
YDI
YDDDD
YS
......

```

example query and expecting result:

if I do query `Y`, my result must be `Y`.  
if I do query `YD`, my result must be `YD`.  
if I do query `YI`, my result must be `YI`.  
if I do query `YDDDDD`, my result must be `YD`.  
if I do query `YDD`, my result must be `YD`....

**They must be unique array.**

---

<div class="post-metadata">

**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\
**Post date:** [February 29, 2016, 2:19pm UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/18 "2016-02-29T14:19:37Z")

</div>

The Edge NGram tokenizer or the "my\_edge\_ngram\_analyzer" tells ES how to tokenize a value before indexing the information. When the data comes in as Y, YD, YDD, YDDD, YDDDD, etc. ES will index Y and YD as tokens, it won't index YDD, YDDD, etc b/c of this custom analyzer. Also, it won't change the original values either. When you search with YD, YDD, YDDD, etc.. ES will be able to serve the request and the returned results will be in its original string as you have seen. If you only wants YD as you said, you might need an extra step to turn the output value to the ngram value that you prefer to see.

This link might have what you are looking for

> <https://stackoverflow.com/questions/13178550/elasticsearch-return-the-tokens-of-a-field>

Since you are using PHP, I'm assuming you are using ES REST web services for this kind of search, you can check out this URL to product the tokens you want

http://[es host]:[es port]/[index name]/\_analyze?analyzer=my\_edge\_ngram\_analyzer&text=YDDD

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [March 1, 2016, 7:29am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/19 "2016-03-01T07:29:23Z")

</div>

Thank you. I tried.

`http://localhost:9200/ihaleler/_analyze?analyzer=my_edge_ngram_analyzer&text=YDDD`

**result:**

{"tokens":[{"token":"Y","start\_offset":0,"end\_offset":1,"type":"word","position":0},{"token":"YD","start\_offset":0,"end\_offset":2,"type":"word","position":1}]}

---

<div class="post-metadata">

**Author:** ![Hilal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hilal/32/7684_2.png) [@Hilal](https://discuss.elastic.co/u/Hilal)\
**Post date:** [March 1, 2016, 7:42am UTC](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239/20 "2016-03-01T07:42:00Z")

</div>

I have another idea.

I did nested mapping this field.

```
PUT ihaleler/ihale/_mapping
{ "properties": {
    "islem": {
      "type": "nested"
    }}} 

```

result:

```
{
  "acknowledged": true
}

```

After I did unique array in php code. I indexed with my php codes:

```
....
    $islem=$ihale['islem'];
                $islem_array = array();
                   $islem_array[] = 'Y';
                   //$islem=$ihale['islem']; // abcdef
                   for($i=0;$i<strlen($islem);$i++){
                       $islem_array[]=substr($islem,0,1);
                   }
                $islem=array_unique($islem_array);    
    $dizi['islem']=$ihale['islem'];

```

but it has failed:

```
Fatal error: Uncaught exception 
'Guzzle\Http\Exception\ClientErrorResponseException' with message 'Client error response[status code] 400 [reason phrase] Bad Request [url] http://localhost:9200/ihaleler/ihale/1030235' in/home/admin web/server.com/public_html/vendor/guzzle/http/Guzzle/Http/Exception/BadResponseException.php:43 Stack trace: #0 /home/adminweb/server.com/public_html/vendor/guzzle/http/Guzzle/Http/Message/Request.php(145): Guzzle\HttpExceptionBadResponseException::factory(Object(Guzzle\Http\Message\EntityEnclosingRequest),Object(Guzzle\Http\Message\Response))#1 [internal function]:Guzzle\Http\MessageRequest::onRequestError(Object(Guzzle\Common\Event),
     'request.error',Object(Symfony\Component\EventDispatcher\EventDispatcher))#2 /home/admin/web/server.com/public_html/vendor/symfony/event-dispatcher/Symfony/Component/EventDispatcher/EventDispatcher.php(164):call_user_func(Array, Object(Guzzle\Common\Event), 'request.error',Object(Symfony\Component\EventDispatcher\EventDispatcher))#3 /home/admin/web/ in /home/adminweb/server.com/public_html/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/GuzzleConnection.php on line 266
```

[Next page](https://discuss.elastic.co/t/mysql-data-mapping-for-elasticseach/42239.md?page=2)