问:
Regex allowed typing confirmation
Does below regex correct to examine whether a-z A-Z spaces are the only one allowed to type?

```<code>   Dim pattern As String = "^([a-zA-Z0-9 ]+\s)*[a-zA-Z0-9 ]+$"
    Dim r As New Regex(pattern)

    If Not r.IsMatch(TextBoxX1.Text) Then
        MsgBox("not allowed")
    End If
</code>```

答:
As far as I know it does what you require, but that's a really unnecessary expression. Instead of checking if each character is A-Z, 0-9 or a space, look for anything that is not that.

```<code>Dim pattern As String = "[^A-Z0-9 ]"
Dim r As New Regex(pattern, RegexOptions.IgnoreCase)

If r.IsMatch(TextBoxX1.Text) Then
    MessageBox.Show("Invalid input!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
</code>```

Note: By specifying RegexOptions.IgnoreCase the match is case-insensitive, so even if I specified A-Z it will match a-z as well.

Online test: https://dotnetfiddle.net/Q23XiA

Pattern explanation:

[^              => Match any character that IS NOT...
    A-Z0-9      => ...A-Z, 0-9 or a space (there's a space on the end of that).
]               => End of character group.
```

Title: Regex allowed typing confirmation

Body:
Does below regex correct to examine whether a-z A-Z spaces are the only one allowed to type?

   Dim pattern As String = "^([a-zA-Z0-9 ]+\s)*[a-zA-Z0-9 ]+$"
    Dim r As New Regex(pattern)

    If Not r.IsMatch(TextBoxX1.Text) Then
        MsgBox("not allowed")
    End If

Body:
As far as I know it does what you require, but that's a really unnecessary expression. Instead of checking if each character is A-Z, 0-9 or a space, look for anything that is not that.

Dim pattern As String = "[^A-Z0-9 ]"
Dim r As New Regex(pattern, RegexOptions.IgnoreCase)

If r.IsMatch(TextBoxX1.Text) Then
    MessageBox.Show("Invalid input!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

Note: By specifying RegexOptions.IgnoreCase the match is case-insensitive, so even if I specified A-Z it will match a-z as well.

Online test: https://dotnetfiddle.net/Q23XiA

Pattern explanation:

[^              => Match any character that IS NOT...
    A-Z0-9      => ...A-Z, 0-9 or a space (there's a space on the end of that).
]               => End of character group.


来源: stackexchange

问:
JSONException on success
I think that JSONException only works when it my request fails but when the request is valid (valid username, password) it should redirect me to another activity but instead a JSONException is showing up.

It shows the JSON string received from the server rather than redirecting me to another activity.

This is my onResponse function

```<code>        @Override
        public void onResponse(String response){
            try {
                JSONObject volleyResponse   = new JSONObject(response);

                boolean success       = volleyResponse.getBoolean("success");
                String message        = volleyResponse.getString("message");

                String UUID     = volleyResponse.getString("unique_user_id");
                String LLOGIN   = volleyResponse.getString("last_login");
                String RDATE    = volleyResponse.getString("registration_date");
                String MONEY    = volleyResponse.getString("money");

                if(success){
                    Intent intent = new Intent(Authentication.this, Mainpage.class);
                    intent.putExtra(KEY_USERNAME, strUsername);
                    intent.putExtra(KEY_UUID, UUID);
                    intent.putExtra(KEY_LLOGIN, LLOGIN);
                    intent.putExtra(KEY_RDATE, RDATE);
                    intent.putExtra(KEY_MONEY, MONEY);
                    startActivity(intent);
                }
            } catch(JSONException e) {
                response = response.replace("\"", "");
                response = response.replace("status:false,message:", "");
                response = response.replace("{", "");
                response = response.replace("}", "");
                messageText.setText(response);
            }
        }
</code>```

JSON Response when it is success:

```<code>{"unique_user_id":"4e99a28a-0cb2-30a9-ac51-ccd4629bcef1","last_name":"therealaxis","password":"$2a$10$9qRjW\/vJreCQg3u5dO6eW.8PhZBTpGaPNK5qRIYP.XTx2PVY1yrOi","last_login":"1 week ago","registration_date":"1 week ago","money":"100.00","success":true}
</code>```

答: Your JSON Response has no message string, so a JSONException is thrown. If you just want to access the message attribute in case it is present, use JSONObject.has before accessing it.</p>

ContentLicense: CC BY-SA 3.0

Title: JSONException on success

Body:
I think that JSONException only works when it my request fails but when the request is valid (valid username, password) it should redirect me to another activity but instead a JSONException is showing up.

It shows the JSON string received from the server rather than redirecting me to another activity.

This is my onResponse function

        @Override
        public void onResponse(String response){
            try {
                JSONObject volleyResponse   = new JSONObject(response);

                boolean success       = volleyResponse.getBoolean("success");
                String message        = volleyResponse.getString("message");

                String UUID     = volleyResponse.getString("unique_user_id");
                String LLOGIN   = volleyResponse.getString("last_login");
                String RDATE    = volleyResponse.getString("registration_date");
                String MONEY    = volleyResponse.getString("money");

                if(success){
                    Intent intent = new Intent(Authentication.this, Mainpage.class);
                    intent.putExtra(KEY_USERNAME, strUsername);
                    intent.putExtra(KEY_UUID, UUID);
                    intent.putExtra(KEY_LLOGIN, LLOGIN);
                    intent.putExtra(KEY_RDATE, RDATE);
                    intent.putExtra(KEY_MONEY, MONEY);
                    startActivity(intent);
                }
            } catch(JSONException e) {
                response = response.replace("\"", "");
                response = response.replace("status:false,message:", "");
                response = response.replace("{", "");
                response = response.replace("}", "");
                messageText.setText(response);
            }
        }

JSON Response when it is success:

{"unique_user_id":"4e99a28a-0cb2-30a9-ac51-ccd4629bcef1","last_name":"therealaxis","password":"$2a$10$9qRjW\/vJreCQg3u5dO6eW.8PhZBTpGaPNK5qRIYP.XTx2PVY1yrOi","last_login":"1 week ago","registration_date":"1 week ago","money":"100.00","success":true}


问:
r, assign an NA value to each row in my b column corresponding to NA in my a column
i'm trying to assign an NA value to each row in my b column corresponding to NA in my a column.
The columns are in a data frame df.

But when i do the following code, all my b column gets NA.

What should i change ?

```<code>  for(i in 1:nrow(df))
{
  row <- df[i,]
  is.na(df$`a`) <- (df$b  <- NA)
}
</code>```

答:
According to the documentation ?is.na, is.na<- is a generic function which sets elements to NA. For the right hand side of is.na(x) <- value, value needs to be a suitable index vector for use with x.

Examples:

```<code>df <- data.frame(a = LETTERS[1:5], b = 1:5)
is.na(df$b) <- c(2, 4)
df
#  a  b
#1 A  1
#2 B NA
#3 C  3
#4 D NA
#5 E  5
</code>```

So, the 2nd and 4th elements of vector df$b have been set to NA.

Now, if the corresponding elements of df$a should be set to NAas well, we can use:

```<code>is.na(df$a) <- is.na(df$b)
df
#     a  b
#1    A  1
#2 <NA> NA
#3    C  3
#4 <NA> NA
#5    E  5
</code>```

NB: I've learned about this feature from answers and comments on Why does is.na() change its argument?

Body:
i'm trying to assign an NA value to each row in my b column corresponding to NA in my a column.
The columns are in a data frame df.

But when i do the following code, all my b column gets NA.

What should i change ?

  for(i in 1:nrow(df))
{
  row <- df[i,]
  is.na(df$`a`) <- (df$b  <- NA)
}

Body:
According to the documentation ?is.na, is.na<- is a generic function which sets elements to NA. For the right hand side of is.na(x) <- value, value needs to be a suitable index vector for use with x.

Examples:

df <- data.frame(a = LETTERS[1:5], b = 1:5)
is.na(df$b) <- c(2, 4)
df
#  a  b
#1 A  1
#2 B NA
#3 C  3
#4 D NA
#5 E  5

So, the 2nd and 4th elements of vector df$b have been set to NA.

Now, if the corresponding elements of df$a should be set to NAas well, we can use:

is.na(df$a) <- is.na(df$b)
df
#     a  b
#1    A  1
#2 <NA> NA
#3    C  3
#4 <NA> NA
#5    E  5

NB: I've learned about this feature from answers and comments on Why does is.na() change its argument?


问:
How to get proguard to obfuscate enum fields and methods
Say I have an enum like this:

```<code>enum Color { RED,BLUE,GREEN }
</code>```

After using proguard, and then JADX to de-compile the source code, I noticed that the enums have been only partially obfuscated.

In the de-compiled code, I get something like this:

```<code>f.RED
</code>```

How can I get also get the fields/constants and methods to be obfuscated too?

EDIT!

Don't think this is possible (for good reason), and you should NOT be trying to do this anyway.

Think about what would happen in the case of Color.RED.name() if RED had been obfuscated and you were expecting the String RED.

答: Don't keep Color enum class and remove the -keep command from proguard file and the all the class will be obfuscated.</p>

ContentLicense: CC BY-SA 4.0

Body:
Say I have an enum like this:

enum Color { RED,BLUE,GREEN }

After using proguard, and then JADX to de-compile the source code, I noticed that the enums have been only partially obfuscated.

In the de-compiled code, I get something like this:

f.RED

How can I get also get the fields/constants and methods to be obfuscated too?

EDIT!

Don't think this is possible (for good reason), and you should NOT be trying to do this anyway.

Think about what would happen in the case of Color.RED.name() if RED had been obfuscated and you were expecting the String RED.


问:
Push existing project into Github
I have a folder with my project sources. How I can push this project into Github's repository?

I tried using this steps:

I created empty repository on GitHub.

I run git-bash and typed git init, so inside project root appeared .git folder.

I added some files to version control using git add sourcesFolderName

I committed files added in previous step using git commit -m "initial commit"

I specified remote repository using git remote add MyProject <url>

Finally git push, but nothing is pushed to remote repo... (no authorization failure)

So how I can push existing sources into newly created github repo?

答:
Hate to add yet another answer, but my particular scenario isn't quite covered here. I had a local repo with a history of changes I wanted to preserve, and a non-empty repo created for me on Github (that is, with the default README.md). Yes, you can always re-create the Github repo as an empty repo, but in my case someone else has the permissions to create this particular repo, and I didn't want to trouble him, if there was an easy workaround.

In this scenario, you will encounter this error when you attempt to git push after setting the remote origin:

```<code> ! [rejected]        master -> master (fetch first)
error: failed to push some refs to 'git@github.com:<my repo>.git'
hint: Updates were rejected because the remote contains work that you do
hint: not have locally. This is usually caused by another repository pushing
hint: to the same ref. You may want to first integrate the remote changes
hint: (e.g., 'git pull ...') before pushing again.
hint: See the 'Note about fast-forwards' in 'git push --help' for details.
</code>```

As the error indicates, I needed to do a git pull after setting the remote origin, but I needed to specify the --allow-unrelated-histories option. Without this option, git pull complains warning: no common commits.

So here is the exact sequence of commands that worked for me:

```<code>git remote add origin <github repo url>
cp README.md README.md-save
git pull origin master --allow-unrelated-histories
mv README.md-save README.md
git commit -a
git push
</code>```

Title: Push existing project into Github

Body:
Hate to add yet another answer, but my particular scenario isn't quite covered here. I had a local repo with a history of changes I wanted to preserve, and a non-empty repo created for me on Github (that is, with the default README.md). Yes, you can always re-create the Github repo as an empty repo, but in my case someone else has the permissions to create this particular repo, and I didn't want to trouble him, if there was an easy workaround.

In this scenario, you will encounter this error when you attempt to git push after setting the remote origin:

 ! [rejected]        master -> master (fetch first)
error: failed to push some refs to 'git@github.com:<my repo>.git'
hint: Updates were rejected because the remote contains work that you do
hint: not have locally. This is usually caused by another repository pushing
hint: to the same ref. You may want to first integrate the remote changes
hint: (e.g., 'git pull ...') before pushing again.
hint: See the 'Note about fast-forwards' in 'git push --help' for details.

As the error indicates, I needed to do a git pull after setting the remote origin, but I needed to specify the --allow-unrelated-histories option. Without this option, git pull complains warning: no common commits.

So here is the exact sequence of commands that worked for me:

git remote add origin <github repo url>
cp README.md README.md-save
git pull origin master --allow-unrelated-histories
mv README.md-save README.md
git commit -a
git push


问:
How do I rewrite URLs without access to IIS using C# and ASP.NET?
I am looking for a solution to rewrite URLs that have query strings so that the query string creates permanent link. For example: the query string ?u=test would become http://www.website.com/profile/test

I am new to URL rewriting, but am open to any suggestions you may have. I don't have access to IIS as I'm using shared virtual hosting.

答:
You can use ASP.NET routing engine (introduced in .NET 3.5 SP1), see this article for quick-start: http://msdn.microsoft.com/en-us/magazine/dd347546.aspx

BTW, although outdated, you should read Scott Gu's post sighted by Danish for various options.

ContentLicense: CC BY-SA 2.5


问:
Building conda skeleton from pypi package throws error
I am trying to follow tutorial at http://conda.pydata.org/docs/build_tutorials/pkgs.html to create conda skeleton from pypi pyinstrument package.

when I call command conda skeleton pypi pyinstrument, it throws following error:

```<code>C:\WINDOWS\system32>conda skeleton pypi pyinstrument
Warning, the following versions were found for pyinstrument
0.10.1
0.11
0.12
0.13
0.13.1
Using 0.13.1
Use --version to specify a different version.
Using url https://pypi.python.org/packages/64/56/d7a0d48973dcf58ea74d5f004e16e9496
Downloading pyinstrument
Using cached download
Unpacking pyinstrument...
done
working in C:\Users\Peter\AppData\Local\Temp\tmpi40k4yslconda_skeleton_pyinstrumen

The following NEW packages will be INSTALLED:

    pip:            8.1.2-py35_0
    python:         3.5.2-0
    pyyaml:         3.12-py35_0
    setuptools:     27.2.0-py35_1
    vs2015_runtime: 14.0.25123-0
    wheel:          0.29.0-py35_0

Applying patch: 'C:\\Users\\Peter\\AppData\\Local\\Temp\\tmpi40k4yslconda_skeleton
        Error:
            Cannot use 'git' (not a git repo and/or patch) and did notfind 'patch' in:  C:\Users\Peter\Anaconda3\conda-bld\skeleton_1478133848196\_b_env\Scripts;.....
            ...You can install 'patch' using apt-get, yum (Linux), Xcode (MacOSX),
            or conda, m2-patch (Windows),
</code>```

This error is also thrown when trying to install py-stackexchange package, what am I doing wrong?

答:
As comment suggested, install patch or m2-patch.
In my case, I installed m2-patch and it fixed the issue for me.

Body:
I am trying to follow tutorial at http://conda.pydata.org/docs/build_tutorials/pkgs.html to create conda skeleton from pypi pyinstrument package.

when I call command conda skeleton pypi pyinstrument, it throws following error:

C:\WINDOWS\system32>conda skeleton pypi pyinstrument
Warning, the following versions were found for pyinstrument
0.10.1
0.11
0.12
0.13
0.13.1
Using 0.13.1
Use --version to specify a different version.
Using url https://pypi.python.org/packages/64/56/d7a0d48973dcf58ea74d5f004e16e9496
Downloading pyinstrument
Using cached download
Unpacking pyinstrument...
done
working in C:\Users\Peter\AppData\Local\Temp\tmpi40k4yslconda_skeleton_pyinstrumen

The following NEW packages will be INSTALLED:

    pip:            8.1.2-py35_0
    python:         3.5.2-0
    pyyaml:         3.12-py35_0
    setuptools:     27.2.0-py35_1
    vs2015_runtime: 14.0.25123-0
    wheel:          0.29.0-py35_0

Applying patch: 'C:\\Users\\Peter\\AppData\\Local\\Temp\\tmpi40k4yslconda_skeleton
        Error:
            Cannot use 'git' (not a git repo and/or patch) and did notfind 'patch' in:  C:\Users\Peter\Anaconda3\conda-bld\skeleton_1478133848196\_b_env\Scripts;.....
            ...You can install 'patch' using apt-get, yum (Linux), Xcode (MacOSX),
            or conda, m2-patch (Windows),

This error is also thrown when trying to install py-stackexchange package, what am I doing wrong?


问:
Multiple OPTIONAL MATCH with COUNT doesn't seem to work
I have an Item node, which is POSTED_BY a User node.  Additionally, the Item may be LIKED by zero or more User nodes, and zero or more Comments may be POSTED_IN the Item node.

What I need to do is to query for a specific Item node (given the id), and retrieve:

The Item node itself

The User node that the Item was POSTED_BY

The count of the number of Users who LIKE the Item node

The count of the number of Comments POSTED_IN the Item node

If I try to query just 1-3, and exclude 4 for now, my query looks something like this:

```<code>MATCH (i:Item {id: 'ByvIzUdbZ'})-[:POSTED_BY]->(u:User)
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
RETURN i, u, COUNT(u2);
</code>```

This returns the Item node, the User and the LIKES count correctly.

Now, to accommodate the 4th point, I modified the query to add another OPTIONAL MATCH, as below:

```<code>MATCH (i:Item {id: 'ByvIzUdbZ'})-[:POSTED_BY]->(u:User)
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
OPTIONAL MATCH (comment:Comment)-[:POSTED_IN]->(i)
RETURN i, u, COUNT(u2), COUNT(comment);
</code>```

With this, however, the counts for both User (u2) and Comment (comment) are incorrect.  They seem to be getting "added up".  So, if the Likes was actually 3, the likes and comment count are both returned as 6.  Any tips on what I'm doing wrong here?  Thanks.

答:
Cybersam has a good explanation of why you're seeing what you're seeing. Keep in mind that the cardinality (number of rows) also affects how many times the operations in your query (such as MATCH and OPTIONAL MATCH) are run.

For example, in your first MATCH, if 10 users posted the same item, then you will have 10 rows of that one item in combination with each of the 10 users.

Then the OPTIONAL MATCH executes, and though you mean it to only execute once per item (you're trying to get the like count per item), it will execute 10 times on the same item, since there are 10 rows with that item. That's extra work that you can avoid by attempting to get the item count to one row before you execute that match, either through aggregation of users, or getting the data you need for each item before you match to users.

Here's an example query that might work a bit better, saving the matching to users until the end, keeping cardinality to 1 for each of the OPTIONAL MATCHes.

```<code>MATCH (i:Item {id: 'ByvIzUdbZ'})       // one row
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
WITH i, COUNT(u2) AS u2_count          // back to 1 row
OPTIONAL MATCH (comment:Comment)-[:POSTED_IN]->(i)
WITH i, u2_count, COUNT(comment) AS comment_count; // back to one row again
MATCH (i)-[:POSTED_BY]->(u:User)
RETURN i, u, u2_count, comment_count
</code>```

Body:
I have an Item node, which is POSTED_BY a User node.  Additionally, the Item may be LIKED by zero or more User nodes, and zero or more Comments may be POSTED_IN the Item node.

What I need to do is to query for a specific Item node (given the id), and retrieve:

The Item node itself

The User node that the Item was POSTED_BY

The count of the number of Users who LIKE the Item node

The count of the number of Comments POSTED_IN the Item node

If I try to query just 1-3, and exclude 4 for now, my query looks something like this:

MATCH (i:Item {id: 'ByvIzUdbZ'})-[:POSTED_BY]->(u:User)
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
RETURN i, u, COUNT(u2);

This returns the Item node, the User and the LIKES count correctly.

Now, to accommodate the 4th point, I modified the query to add another OPTIONAL MATCH, as below:

MATCH (i:Item {id: 'ByvIzUdbZ'})-[:POSTED_BY]->(u:User)
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
OPTIONAL MATCH (comment:Comment)-[:POSTED_IN]->(i)
RETURN i, u, COUNT(u2), COUNT(comment);

With this, however, the counts for both User (u2) and Comment (comment) are incorrect.  They seem to be getting "added up".  So, if the Likes was actually 3, the likes and comment count are both returned as 6.  Any tips on what I'm doing wrong here?  Thanks.

Body:
Cybersam has a good explanation of why you're seeing what you're seeing. Keep in mind that the cardinality (number of rows) also affects how many times the operations in your query (such as MATCH and OPTIONAL MATCH) are run.

For example, in your first MATCH, if 10 users posted the same item, then you will have 10 rows of that one item in combination with each of the 10 users.

Then the OPTIONAL MATCH executes, and though you mean it to only execute once per item (you're trying to get the like count per item), it will execute 10 times on the same item, since there are 10 rows with that item. That's extra work that you can avoid by attempting to get the item count to one row before you execute that match, either through aggregation of users, or getting the data you need for each item before you match to users.

Here's an example query that might work a bit better, saving the matching to users until the end, keeping cardinality to 1 for each of the OPTIONAL MATCHes.

MATCH (i:Item {id: 'ByvIzUdbZ'})       // one row
OPTIONAL MATCH (u2:User)-[:LIKES]->(i)
WITH i, COUNT(u2) AS u2_count          // back to 1 row
OPTIONAL MATCH (comment:Comment)-[:POSTED_IN]->(i)
WITH i, u2_count, COUNT(comment) AS comment_count; // back to one row again
MATCH (i)-[:POSTED_BY]->(u:User)
RETURN i, u, u2_count, comment_count


问:
border-left height in css
I have a fiddle and I am trying to copy as it is as shown in this image. At this moment, I am able to put borders across an image but for some reasons I am not able to increase the height of border-left. I am pretty sure it will done through CSS selectors but not sure how. The CSS which I have used to put the borders across an image is:</p>

```<code>.helmet-image1
{
position: relative;
left: 20px;
border-left: 2px solid rgb(254, 91, 31);
border-right: 2px solid rgb(254, 91, 31);
border-top: 2px solid rgb(254, 91, 31);
}
</code>```

答:
you could use box-shadow on img tag eventually:

img {
  padding: 5px;/*optionnal */
  vertical-align: top;/*optionnal */
  box-shadow: -2px -2px rgb(254, 91, 31), 0 171px white, -2px 170px rgb(254, 91, 31), 2px -2px rgb(254, 91, 31), 2px -1px rgb(254, 91, 31), 0px -2px rgb(254, 91, 31);
  border-radius: 5px 5px 0 0/*optionnal */
}

/* demo purpose for small snippet */
body {min-height:400px;}```
<pre class="snippet-code-html lang-html prettyprint-override"><code><img class="helmet-image1" src="https://s24.postimg.org/m3yekb9n9/i_Stock-
516143705.png">
<br/> text to test</code>```

https://jsfiddle.net/9b7uaoj2/15/

Title: border-left height in css

Body:
I have a fiddle and I am trying to copy as it is as shown in this image. At this moment, I am able to put borders across an image but for some reasons I am not able to increase the height of border-left. I am pretty sure it will done through CSS selectors but not sure how. The CSS which I have used to put the borders across an image is:

.helmet-image1
{
position: relative;
left: 20px;
border-left: 2px solid rgb(254, 91, 31);
border-right: 2px solid rgb(254, 91, 31);
border-top: 2px solid rgb(254, 91, 31);
}

Body:
you could use box-shadow on img tag eventually:

img {
  padding: 5px;/*optionnal */
  vertical-align: top;/*optionnal */
  box-shadow: -2px -2px rgb(254, 91, 31), 0 171px white, -2px 170px rgb(254, 91, 31), 2px -2px rgb(254, 91, 31), 2px -1px rgb(254, 91, 31), 0px -2px rgb(254, 91, 31);
  border-radius: 5px 5px 0 0/*optionnal */
}

/* demo purpose for small snippet */
body {min-height:400px;}

<img class="helmet-image1" src="https://s24.postimg.org/m3yekb9n9/i_Stock-
516143705.png">
<br/> text to test

https://jsfiddle.net/9b7uaoj2/15/


问:
Slow performance MySql query, alternatives / suggestions?
I'm implementing a search filter page in my coin website but it's quite slow now that I have many entries:

Here is the query when no filter is selected:

```<code>SELECT
    i.id_items AS id,
    GROUP_CONCAT(DISTINCT iant.translation ORDER BY iant.translation asc SEPARATOR ', ') AS names,
    GROUP_CONCAT(DISTINCT iafv.face_value ORDER BY iafv.face_value ASC SEPARATOR ', ') AS facesValues,
    GROUP_CONCAT(DISTINCT iacut.translation ORDER BY iacut.translation ASC SEPARATOR ', ') AS currencies,
    GROUP_CONCAT(DISTINCT cout.translation ORDER BY cout.translation ASC SEPARATOR ', ') AS countries,
    GROUP_CONCAT(DISTINCT iacot.translation ORDER BY iacot.translation ASC SEPARATOR ', ') AS compositions,
    GROUP_CONCAT(DISTINCT iacc.catalog_code ORDER BY iacc.catalog_code ASC SEPARATOR ', ') AS catalogCodes,
    GROUP_CONCAT(DISTINCT iaio.issues_on ORDER BY iaio.issues_on ASC SEPARATOR ', ') AS issuesOn,
    GROUP_CONCAT(DISTINCT iali.last_issues ORDER BY iali.last_issues ASC SEPARATOR ', ') AS latestIssues,
    MIN(ucfs.price) AS minPrice,
    SUM(ucfs.units) AS totalUnits
FROM
    items i
INNER JOIN languages AS l
    ON l.language_code = "en"

LEFT JOIN items_atr_faces_values_match AS iafvm
    ON iafvm.id_items = i.id_items
LEFT JOIN items_atr_faces_values AS iafv
    ON iafv.id_items_atr_faces_values = iafvm.id_items_atr_faces_values

LEFT JOIN items_atr_currencies_match AS iacum
    ON iacum.id_items = i.id_items
LEFT JOIN items_atr_currencies_translations AS iacut
    ON iacut.id_items_atr_currencies = iacum.id_items_atr_currencies
    AND iacut.id_language_code = l.id_languages
LEFT JOIN items_atr_currencies AS iacu
    ON iacu.id_items_atr_currencies = iacum.id_items_atr_currencies

LEFT JOIN items_atr_countries_match AS iacoum
    ON iacoum.id_items = i.id_items
LEFT JOIN items_atr_countries_translations AS cout
    ON cout.id_items_atr_countries = iacoum.id_items_atr_countries
    AND cout.id_language_code = l.id_languages
LEFT JOIN items_atr_countries AS cou
    ON cou.id_items_atr_countries = iacoum.id_items_atr_countries

LEFT JOIN items_atr_compositions_match AS iacom
    ON iacom.id_items = i.id_items
LEFT JOIN items_atr_compositions_translations AS iacot
    ON iacot.id_items_atr_compositions = iacom.id_items_atr_compositions
    AND iacot.id_language_code = l.id_languages
LEFT JOIN items_atr_compositions AS iaco
    ON iaco.id_items_atr_compositions = iacom.id_items_atr_compositions

LEFT JOIN items_atr_catalog_codes_match AS iaccm
    ON iaccm.id_items = i.id_items
LEFT JOIN items_atr_catalog_codes AS iacc
    ON iacc.id_items_atr_catalog_codes = iaccm.id_items_atr_catalog_codes

LEFT JOIN items_atr_issues_on_match AS iaiom
    ON iaiom.id_items = i.id_items
LEFT JOIN items_atr_issues_on AS iaio
    ON iaio.id_items_atr_issues_on = iaiom.id_items_atr_issues_on

LEFT JOIN items_atr_last_issues_match AS ialim
    ON ialim.id_items = i.id_items
LEFT JOIN items_atr_last_issues AS iali
    ON iali.id_items_atr_last_issues = ialim.id_items_atr_last_issues

LEFT JOIN items_atr_names_match AS ianm
    ON ianm.id_items = i.id_items
LEFT JOIN items_atr_names_translations AS iant
    ON iant.id_items_atr_names = ianm.id_items_atr_names
    AND iant.id_language_code = l.id_languages
LEFT JOIN items_atr_names AS ian
    ON ian.id_items_atr_names = ianm.id_items_atr_names

LEFT JOIN users AS u On u.status = 'active'
LEFT JOIN users_coins_for_sale AS ucfs
    ON ucfs.id_users = u.id_users AND ucfs.id_items = i.id_items

GROUP BY id

ORDER BY 2 asc

LIMIT 0, 20
</code>```

Here the explanation:

I'm using Group Concat since each item can have one or more matches with the attributes (name, face value, etc).

The query takes 1.3 seconds to run with 15k entries. The tables will have up 100k entries eventually, so the time to run the query will be increased too...

At first I thought the Group_Concat was the responsable to slow down the query but then, after removing the LIMIT 0, 20 I saw it takes the same time to run.

I tried to put the Limit in a subquery:

```<code>...
FROM
    (SELECT id_items FROM items LIMIT 0, 20) AS i
INNER JOIN languages AS l
...
</code>```

Now the query is fast but the result is not what I expected.

Do you have guys any suggestion to fix this query?

Also, I am having a similar problem in this query, to count the number of items:

```<code>SELECT
    COUNT(DISTINCT i.id_items) AS number
FROM
    items i
INNER JOIN languages AS l
    ON l.language_code = "en"

LEFT JOIN items_atr_faces_values_match AS iafvm
    ON iafvm.id_items = i.id_items
LEFT JOIN items_atr_faces_values AS iafv
    ON iafv.id_items_atr_faces_values = iafvm.id_items_atr_faces_values

LEFT JOIN items_atr_currencies_match AS iacum
    ON iacum.id_items = i.id_items
LEFT JOIN items_atr_currencies_translations AS iacut
    ON iacut.id_items_atr_currencies = iacum.id_items_atr_currencies
    AND iacut.id_language_code = l.id_languages
LEFT JOIN items_atr_currencies AS iacu
    ON iacu.id_items_atr_currencies = iacum.id_items_atr_currencies

LEFT JOIN items_atr_countries_match AS iacoum
    ON iacoum.id_items = i.id_items
LEFT JOIN items_atr_countries_translations AS cout
    ON cout.id_items_atr_countries = iacoum.id_items_atr_countries
    AND cout.id_language_code = l.id_languages
LEFT JOIN items_atr_countries AS cou
    ON cou.id_items_atr_countries = iacoum.id_items_atr_countries

LEFT JOIN items_atr_compositions_match AS iacom
    ON iacom.id_items = i.id_items
LEFT JOIN items_atr_compositions_translations AS iacot
    ON iacot.id_items_atr_compositions = iacom.id_items_atr_compositions
    AND iacot.id_language_code = l.id_languages
LEFT JOIN items_atr_compositions AS iaco
    ON iaco.id_items_atr_compositions = iacom.id_items_atr_compositions

LEFT JOIN items_atr_catalog_codes_match AS iaccm
    ON iaccm.id_items = i.id_items
LEFT JOIN items_atr_catalog_codes AS iacc
    ON iacc.id_items_atr_catalog_codes = iaccm.id_items_atr_catalog_codes

LEFT JOIN items_atr_issues_on_match AS iaiom
    ON iaiom.id_items = i.id_items
LEFT JOIN items_atr_issues_on AS iaio
    ON iaio.id_items_atr_issues_on = iaiom.id_items_atr_issues_on

LEFT JOIN items_atr_last_issues_match AS ialim
    ON ialim.id_items = i.id_items
LEFT JOIN items_atr_last_issues AS iali
    ON iali.id_items_atr_last_issues = ialim.id_items_atr_last_issues

LEFT JOIN items_atr_names_match AS ianm
    ON ianm.id_items = i.id_items
LEFT JOIN items_atr_names_translations AS iant
    ON iant.id_items_atr_names = ianm.id_items_atr_names
    AND iant.id_language_code = l.id_languages
LEFT JOIN items_atr_names AS ian
    ON ian.id_items_atr_names = ianm.id_items_atr_names

LEFT JOIN users AS u On u.status = 'active'
LEFT JOIN users_coins_for_sale AS ucfs
    ON ucfs.id_users = u.id_users AND ucfs.id_items = i.id_items

LIMIT 1
</code>```

That takes 0.8 seconds.

Explanation:

I run the two queries in my search page (need the second one for the pagination), so it end up taking 2 seconds to load. Now with 15k items, so when I have 100K it will take much longer.

Any suggestion will be appreciate.

Thanks in advance

Update: Here is the creation data:

```<code>CREATE TABLE IF NOT EXISTS `items` (
    `id_items` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_users` int(11) unsigned NOT NULL DEFAULT '0',
    `date` date NOT NULL,
    `views` bigint(20) NOT NULL DEFAULT '0',
    `status` enum('draft','published') NOT NULL DEFAULT 'draft',
    PRIMARY KEY (`id_items`),
    UNIQUE KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    CONSTRAINT `FK_items_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de items';

CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes` (
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `catalog_code` varchar(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_catalog_codes`),
    UNIQUE KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    FULLTEXT KEY `catalog_code` (`catalog_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Los diferentes códigos de catálogo que tienen las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes_match` (
    `id_items_atr_catalog_codes_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_catalog_codes_match`),
    UNIQUE KEY `id_items_atr_catalog_codes_match` (`id_items_atr_catalog_codes_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_catalog_codes` FOREIGN KEY (`id_items_atr_catalog_codes`) REFERENCES `items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de monedas con los códigos del catálogo';

CREATE TABLE IF NOT EXISTS `items_atr_compositions` (
    `id_items_atr_compositions` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_compositions`),
    UNIQUE KEY `id_items_atr_compositions` (`id_items_atr_compositions`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de las diferentes composiciones';

CREATE TABLE IF NOT EXISTS `items_atr_compositions_match` (
    `id_items_atr_compositions_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_match`),
    UNIQUE KEY `id_items_atr_compositions_match` (`id_items_atr_compositions_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Las diferentes composiciones que puede tener un item';

CREATE TABLE IF NOT EXISTS `items_atr_compositions_translations` (
    `id_items_atr_compositions_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_translations`),
    UNIQUE KEY `id_items_atr_compositions_translations` (`id_items_atr_compositions_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_compositions_translations_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones de las composiciones';

CREATE TABLE IF NOT EXISTS `items_atr_countries` (
    `id_items_atr_countries` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_countries`),
    UNIQUE KEY `id_items_atr_countries` (`id_items_atr_countries`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `items_atr_countries_match` (
    `id_items_atr_countries_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_countries_match`),
    UNIQUE KEY `items_atr_countries_match` (`id_items_atr_countries_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A que pais pertenece cada item';

CREATE TABLE IF NOT EXISTS `items_atr_countries_translations` (
    `id_items_atr_countries_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_countries_translations`),
    UNIQUE KEY `id_items_atr_countries_translations` (`id_items_atr_countries_translations`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK__items_atr_countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK_items_atr_countries_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `items_atr_currencies` (
    `id_items_atr_currencies` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_currencies`),
    UNIQUE KEY `id_items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Diferentes divisas de las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_currencies_match` (
    `id_items_atr_currencies_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_match`),
    UNIQUE KEY `id_items_atr_currencies_match` (`id_items_atr_currencies_match`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de las monedas con su divisa';

CREATE TABLE IF NOT EXISTS `items_atr_currencies_translations` (
    `id_items_atr_currencies_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(255) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_translations`),
    UNIQUE KEY `id_items_atr_currencies_translations` (`id_items_atr_currencies_translations`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_currencies_translations_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`),
    CONSTRAINT `FK_items_atr_currencies_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones de las divisas';

CREATE TABLE IF NOT EXISTS `items_atr_faces_values` (
    `id_items_atr_faces_values` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `face_value` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id_items_atr_faces_values`),
    UNIQUE KEY `id_items_atr_faces_values` (`id_items_atr_faces_values`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Valores faciales de las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_faces_values_match` (
    `id_items_atr_faces_values_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_faces_values` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_faces_values_match`),
    UNIQUE KEY `id_items_atr_faces_values_match` (`id_items_atr_faces_values_match`),
    KEY `id_items_atr_faces_values` (`id_items_atr_faces_values`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_faces_values_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_faces_values_match_items_atr_faces_values` FOREIGN KEY (`id_items_atr_faces_values`) REFERENCES `items_atr_faces_values` (`id_items_atr_faces_values`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de las monedas con sus valores faciales';

CREATE TABLE IF NOT EXISTS `items_atr_issues_on` (
    `id_items_atr_issues_on` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `issues_on` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_issues_on`),
    UNIQUE KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    FULLTEXT KEY `issues_on` (`issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fecha en la que se emitió por primera vez la moneda';

CREATE TABLE IF NOT EXISTS `items_atr_issues_on_match` (
    `id_items_atr_issues_on_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_issues_on` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_issues_on_match`),
    UNIQUE KEY `id_items_atr_issues_on_match` (`id_items_atr_issues_on_match`),
    KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items_atr_issues_on` FOREIGN KEY (`id_items_atr_issues_on`) REFERENCES `items_atr_issues_on` (`id_items_atr_issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociaciones de emisiones con las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_last_issues` (
    `id_items_atr_last_issues` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `last_issues` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues`),
    UNIQUE KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    FULLTEXT KEY `last_issues` (`last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Atributo de última emisión de la moneda';

CREATE TABLE IF NOT EXISTS `items_atr_last_issues_match` (
    `id_items_atr_last_issues_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_last_issues` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues_match`),
    UNIQUE KEY `id_items_atr_last_issues_match` (`id_items_atr_last_issues_match`),
    KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items_atr_last_issues` FOREIGN KEY (`id_items_atr_last_issues`) REFERENCES `items_atr_last_issues` (`id_items_atr_last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociaciones entre las últimas emisiones y las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_names` (
    `id_items_atr_names` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_names`),
    UNIQUE KEY `id_items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contiene el atributo nombre de los items';

CREATE TABLE IF NOT EXISTS `items_atr_names_match` (
    `id_items_atr_names_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_match`),
    UNIQUE KEY `id_items_atr_names_match` (`id_items_atr_names_match`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación del atributo nombre con las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_names_translations` (
    `id_items_atr_names_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_translations`),
    UNIQUE KEY `id_items_atr_names_translations` (`id_items_atr_names_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `STR_LEVE_idx` (`translation`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_names_translations_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`),
    CONSTRAINT `FK_items_atr_names_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones del atributo nombre';

CREATE TABLE IF NOT EXISTS `users` (
    `id_users` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_languages` int(11) unsigned NOT NULL,
    `password` char(128) NOT NULL,
    `user_name` char(128) NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `ip_register` varbinary(16) DEFAULT NULL,
    `ip_last_login` varbinary(16) DEFAULT NULL,
    `creation_date` datetime DEFAULT NULL,
    `email` varchar(254) DEFAULT NULL,
    `fb_user_id` varchar(255) DEFAULT NULL,
    `fb_access_token` varchar(255) DEFAULT NULL,
    `regen_password` varchar(254) DEFAULT NULL,
    `activation_token` varchar(254) DEFAULT NULL,
    `affiliated` varchar(30) NOT NULL DEFAULT 'no',
    `credit` decimal(10,2) NOT NULL DEFAULT '0.00',
    `status` enum('inactive','active','vacation','deleted') NOT NULL DEFAULT 'inactive',
    `news` tinyint(1) NOT NULL DEFAULT '1',
    `promotions` tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id_users`),
    UNIQUE KEY `email` (`email`),
    KEY `id_languages` (`id_languages`),
    FULLTEXT KEY `fb_user_id` (`fb_user_id`),
    FULLTEXT KEY `email2` (`email`),
    FULLTEXT KEY `user_name` (`user_name`),
    CONSTRAINT `FK_users_languages` FOREIGN KEY (`id_languages`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de usuarios en la web';

CREATE TABLE IF NOT EXISTS `users_coins_for_sale` (
    `id_users_coins_for_sale` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items` int(11) unsigned NOT NULL,
    `id_users` int(11) unsigned NOT NULL,
    `units` int(5) unsigned NOT NULL,
    `year` varchar(5) NOT NULL,
    `grade` enum('good','very_good','fine','very_fine','extra_fine','about_uncirculated','uncirculated','brilliant_uncirculated','proof') NOT NULL,
    `price` decimal(8,2) unsigned NOT NULL,
    `picture` varchar(500) DEFAULT NULL,
    `comment` varchar(500) DEFAULT NULL,
    `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_users_coins_for_sale`),
    UNIQUE KEY `id_users_coins_for_sale_unique` (`id_users_coins_for_sale`),
    KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    KEY `year` (`year`),
    KEY `grade` (`grade`),
    KEY `id_users_coins_for_sale` (`id_users_coins_for_sale`),
    CONSTRAINT `FK_users_coins_for_sale_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_users_coins_for_sale_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code>```

答:
Too many tables.

Do not "normalize" simple things such as face_value and issues_on (and probably others.  It leads to extra JOINs without extra flexibility, etc.

I see a country id, but no country name??  It may be better to use this instead of the table and id:

```<code>country_code CHAR(2) CHARACTER SET ascii
</code>```

and use the standard 2-letter country codes.  (It will also replace a 4-byte INT with a 2-byte string in various tables.

Also, since a PRIMARY KEY is, by definition (in MySQL), UNIQUE:

```<code>PRIMARY KEY(x)
UNIQUE     (x)  -- DROP this
</code>```

Do you need LEFT?  That is, is the row in the 'right' table possibly missing?  If not, remove LEFT.

After you have cleaned up those, ask for another review.

Body:
I'm implementing a search filter page in my coin website but it's quite slow now that I have many entries:

Here is the query when no filter is selected:

SELECT
    i.id_items AS id,
    GROUP_CONCAT(DISTINCT iant.translation ORDER BY iant.translation asc SEPARATOR ', ') AS names,
    GROUP_CONCAT(DISTINCT iafv.face_value ORDER BY iafv.face_value ASC SEPARATOR ', ') AS facesValues,
    GROUP_CONCAT(DISTINCT iacut.translation ORDER BY iacut.translation ASC SEPARATOR ', ') AS currencies,
    GROUP_CONCAT(DISTINCT cout.translation ORDER BY cout.translation ASC SEPARATOR ', ') AS countries,
    GROUP_CONCAT(DISTINCT iacot.translation ORDER BY iacot.translation ASC SEPARATOR ', ') AS compositions,
    GROUP_CONCAT(DISTINCT iacc.catalog_code ORDER BY iacc.catalog_code ASC SEPARATOR ', ') AS catalogCodes,
    GROUP_CONCAT(DISTINCT iaio.issues_on ORDER BY iaio.issues_on ASC SEPARATOR ', ') AS issuesOn,
    GROUP_CONCAT(DISTINCT iali.last_issues ORDER BY iali.last_issues ASC SEPARATOR ', ') AS latestIssues,
    MIN(ucfs.price) AS minPrice,
    SUM(ucfs.units) AS totalUnits
FROM
    items i
INNER JOIN languages AS l
    ON l.language_code = "en"

LEFT JOIN items_atr_faces_values_match AS iafvm
    ON iafvm.id_items = i.id_items
LEFT JOIN items_atr_faces_values AS iafv
    ON iafv.id_items_atr_faces_values = iafvm.id_items_atr_faces_values

LEFT JOIN items_atr_currencies_match AS iacum
    ON iacum.id_items = i.id_items
LEFT JOIN items_atr_currencies_translations AS iacut
    ON iacut.id_items_atr_currencies = iacum.id_items_atr_currencies
    AND iacut.id_language_code = l.id_languages
LEFT JOIN items_atr_currencies AS iacu
    ON iacu.id_items_atr_currencies = iacum.id_items_atr_currencies

LEFT JOIN items_atr_countries_match AS iacoum
    ON iacoum.id_items = i.id_items
LEFT JOIN items_atr_countries_translations AS cout
    ON cout.id_items_atr_countries = iacoum.id_items_atr_countries
    AND cout.id_language_code = l.id_languages
LEFT JOIN items_atr_countries AS cou
    ON cou.id_items_atr_countries = iacoum.id_items_atr_countries

LEFT JOIN items_atr_compositions_match AS iacom
    ON iacom.id_items = i.id_items
LEFT JOIN items_atr_compositions_translations AS iacot
    ON iacot.id_items_atr_compositions = iacom.id_items_atr_compositions
    AND iacot.id_language_code = l.id_languages
LEFT JOIN items_atr_compositions AS iaco
    ON iaco.id_items_atr_compositions = iacom.id_items_atr_compositions

LEFT JOIN items_atr_catalog_codes_match AS iaccm
    ON iaccm.id_items = i.id_items
LEFT JOIN items_atr_catalog_codes AS iacc
    ON iacc.id_items_atr_catalog_codes = iaccm.id_items_atr_catalog_codes

LEFT JOIN items_atr_issues_on_match AS iaiom
    ON iaiom.id_items = i.id_items
LEFT JOIN items_atr_issues_on AS iaio
    ON iaio.id_items_atr_issues_on = iaiom.id_items_atr_issues_on

LEFT JOIN items_atr_last_issues_match AS ialim
    ON ialim.id_items = i.id_items
LEFT JOIN items_atr_last_issues AS iali
    ON iali.id_items_atr_last_issues = ialim.id_items_atr_last_issues

LEFT JOIN items_atr_names_match AS ianm
    ON ianm.id_items = i.id_items
LEFT JOIN items_atr_names_translations AS iant
    ON iant.id_items_atr_names = ianm.id_items_atr_names
    AND iant.id_language_code = l.id_languages
LEFT JOIN items_atr_names AS ian
    ON ian.id_items_atr_names = ianm.id_items_atr_names

LEFT JOIN users AS u On u.status = 'active'
LEFT JOIN users_coins_for_sale AS ucfs
    ON ucfs.id_users = u.id_users AND ucfs.id_items = i.id_items

GROUP BY id

ORDER BY 2 asc

LIMIT 0, 20

Here the explanation:

I'm using Group Concat since each item can have one or more matches with the attributes (name, face value, etc).

The query takes 1.3 seconds to run with 15k entries. The tables will have up 100k entries eventually, so the time to run the query will be increased too...

At first I thought the Group_Concat was the responsable to slow down the query but then, after removing the LIMIT 0, 20 I saw it takes the same time to run.

I tried to put the Limit in a subquery:

...
FROM
    (SELECT id_items FROM items LIMIT 0, 20) AS i
INNER JOIN languages AS l
...

Now the query is fast but the result is not what I expected.

Do you have guys any suggestion to fix this query?

Also, I am having a similar problem in this query, to count the number of items:

SELECT
    COUNT(DISTINCT i.id_items) AS number
FROM
    items i
INNER JOIN languages AS l
    ON l.language_code = "en"

LEFT JOIN items_atr_faces_values_match AS iafvm
    ON iafvm.id_items = i.id_items
LEFT JOIN items_atr_faces_values AS iafv
    ON iafv.id_items_atr_faces_values = iafvm.id_items_atr_faces_values

LEFT JOIN items_atr_currencies_match AS iacum
    ON iacum.id_items = i.id_items
LEFT JOIN items_atr_currencies_translations AS iacut
    ON iacut.id_items_atr_currencies = iacum.id_items_atr_currencies
    AND iacut.id_language_code = l.id_languages
LEFT JOIN items_atr_currencies AS iacu
    ON iacu.id_items_atr_currencies = iacum.id_items_atr_currencies

LEFT JOIN items_atr_countries_match AS iacoum
    ON iacoum.id_items = i.id_items
LEFT JOIN items_atr_countries_translations AS cout
    ON cout.id_items_atr_countries = iacoum.id_items_atr_countries
    AND cout.id_language_code = l.id_languages
LEFT JOIN items_atr_countries AS cou
    ON cou.id_items_atr_countries = iacoum.id_items_atr_countries

LEFT JOIN items_atr_compositions_match AS iacom
    ON iacom.id_items = i.id_items
LEFT JOIN items_atr_compositions_translations AS iacot
    ON iacot.id_items_atr_compositions = iacom.id_items_atr_compositions
    AND iacot.id_language_code = l.id_languages
LEFT JOIN items_atr_compositions AS iaco
    ON iaco.id_items_atr_compositions = iacom.id_items_atr_compositions

LEFT JOIN items_atr_catalog_codes_match AS iaccm
    ON iaccm.id_items = i.id_items
LEFT JOIN items_atr_catalog_codes AS iacc
    ON iacc.id_items_atr_catalog_codes = iaccm.id_items_atr_catalog_codes

LEFT JOIN items_atr_issues_on_match AS iaiom
    ON iaiom.id_items = i.id_items
LEFT JOIN items_atr_issues_on AS iaio
    ON iaio.id_items_atr_issues_on = iaiom.id_items_atr_issues_on

LEFT JOIN items_atr_last_issues_match AS ialim
    ON ialim.id_items = i.id_items
LEFT JOIN items_atr_last_issues AS iali
    ON iali.id_items_atr_last_issues = ialim.id_items_atr_last_issues

LEFT JOIN items_atr_names_match AS ianm
    ON ianm.id_items = i.id_items
LEFT JOIN items_atr_names_translations AS iant
    ON iant.id_items_atr_names = ianm.id_items_atr_names
    AND iant.id_language_code = l.id_languages
LEFT JOIN items_atr_names AS ian
    ON ian.id_items_atr_names = ianm.id_items_atr_names

LEFT JOIN users AS u On u.status = 'active'
LEFT JOIN users_coins_for_sale AS ucfs
    ON ucfs.id_users = u.id_users AND ucfs.id_items = i.id_items

LIMIT 1

That takes 0.8 seconds.

Explanation:

I run the two queries in my search page (need the second one for the pagination), so it end up taking 2 seconds to load. Now with 15k items, so when I have 100K it will take much longer.

Any suggestion will be appreciate.

Thanks in advance

Update: Here is the creation data:

CREATE TABLE IF NOT EXISTS `items` (
    `id_items` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_users` int(11) unsigned NOT NULL DEFAULT '0',
    `date` date NOT NULL,
    `views` bigint(20) NOT NULL DEFAULT '0',
    `status` enum('draft','published') NOT NULL DEFAULT 'draft',
    PRIMARY KEY (`id_items`),
    UNIQUE KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    CONSTRAINT `FK_items_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de items';

CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes` (
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `catalog_code` varchar(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_catalog_codes`),
    UNIQUE KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    FULLTEXT KEY `catalog_code` (`catalog_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Los diferentes códigos de catálogo que tienen las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes_match` (
    `id_items_atr_catalog_codes_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_catalog_codes_match`),
    UNIQUE KEY `id_items_atr_catalog_codes_match` (`id_items_atr_catalog_codes_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_catalog_codes` FOREIGN KEY (`id_items_atr_catalog_codes`) REFERENCES `items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de monedas con los códigos del catálogo';

CREATE TABLE IF NOT EXISTS `items_atr_compositions` (
    `id_items_atr_compositions` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_compositions`),
    UNIQUE KEY `id_items_atr_compositions` (`id_items_atr_compositions`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de las diferentes composiciones';

CREATE TABLE IF NOT EXISTS `items_atr_compositions_match` (
    `id_items_atr_compositions_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_match`),
    UNIQUE KEY `id_items_atr_compositions_match` (`id_items_atr_compositions_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Las diferentes composiciones que puede tener un item';

CREATE TABLE IF NOT EXISTS `items_atr_compositions_translations` (
    `id_items_atr_compositions_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_translations`),
    UNIQUE KEY `id_items_atr_compositions_translations` (`id_items_atr_compositions_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_compositions_translations_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones de las composiciones';

CREATE TABLE IF NOT EXISTS `items_atr_countries` (
    `id_items_atr_countries` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_countries`),
    UNIQUE KEY `id_items_atr_countries` (`id_items_atr_countries`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `items_atr_countries_match` (
    `id_items_atr_countries_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_countries_match`),
    UNIQUE KEY `items_atr_countries_match` (`id_items_atr_countries_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A que pais pertenece cada item';

CREATE TABLE IF NOT EXISTS `items_atr_countries_translations` (
    `id_items_atr_countries_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_countries_translations`),
    UNIQUE KEY `id_items_atr_countries_translations` (`id_items_atr_countries_translations`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK__items_atr_countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK_items_atr_countries_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `items_atr_currencies` (
    `id_items_atr_currencies` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_currencies`),
    UNIQUE KEY `id_items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Diferentes divisas de las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_currencies_match` (
    `id_items_atr_currencies_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_match`),
    UNIQUE KEY `id_items_atr_currencies_match` (`id_items_atr_currencies_match`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de las monedas con su divisa';

CREATE TABLE IF NOT EXISTS `items_atr_currencies_translations` (
    `id_items_atr_currencies_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(255) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_translations`),
    UNIQUE KEY `id_items_atr_currencies_translations` (`id_items_atr_currencies_translations`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_currencies_translations_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`),
    CONSTRAINT `FK_items_atr_currencies_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones de las divisas';

CREATE TABLE IF NOT EXISTS `items_atr_faces_values` (
    `id_items_atr_faces_values` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `face_value` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id_items_atr_faces_values`),
    UNIQUE KEY `id_items_atr_faces_values` (`id_items_atr_faces_values`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Valores faciales de las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_faces_values_match` (
    `id_items_atr_faces_values_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_faces_values` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_faces_values_match`),
    UNIQUE KEY `id_items_atr_faces_values_match` (`id_items_atr_faces_values_match`),
    KEY `id_items_atr_faces_values` (`id_items_atr_faces_values`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_faces_values_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_faces_values_match_items_atr_faces_values` FOREIGN KEY (`id_items_atr_faces_values`) REFERENCES `items_atr_faces_values` (`id_items_atr_faces_values`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación de las monedas con sus valores faciales';

CREATE TABLE IF NOT EXISTS `items_atr_issues_on` (
    `id_items_atr_issues_on` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `issues_on` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_issues_on`),
    UNIQUE KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    FULLTEXT KEY `issues_on` (`issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fecha en la que se emitió por primera vez la moneda';

CREATE TABLE IF NOT EXISTS `items_atr_issues_on_match` (
    `id_items_atr_issues_on_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_issues_on` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_issues_on_match`),
    UNIQUE KEY `id_items_atr_issues_on_match` (`id_items_atr_issues_on_match`),
    KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items_atr_issues_on` FOREIGN KEY (`id_items_atr_issues_on`) REFERENCES `items_atr_issues_on` (`id_items_atr_issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociaciones de emisiones con las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_last_issues` (
    `id_items_atr_last_issues` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `last_issues` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues`),
    UNIQUE KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    FULLTEXT KEY `last_issues` (`last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Atributo de última emisión de la moneda';

CREATE TABLE IF NOT EXISTS `items_atr_last_issues_match` (
    `id_items_atr_last_issues_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_last_issues` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues_match`),
    UNIQUE KEY `id_items_atr_last_issues_match` (`id_items_atr_last_issues_match`),
    KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items_atr_last_issues` FOREIGN KEY (`id_items_atr_last_issues`) REFERENCES `items_atr_last_issues` (`id_items_atr_last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociaciones entre las últimas emisiones y las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_names` (
    `id_items_atr_names` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_names`),
    UNIQUE KEY `id_items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contiene el atributo nombre de los items';

CREATE TABLE IF NOT EXISTS `items_atr_names_match` (
    `id_items_atr_names_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_match`),
    UNIQUE KEY `id_items_atr_names_match` (`id_items_atr_names_match`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Asociación del atributo nombre con las monedas';

CREATE TABLE IF NOT EXISTS `items_atr_names_translations` (
    `id_items_atr_names_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_translations`),
    UNIQUE KEY `id_items_atr_names_translations` (`id_items_atr_names_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `STR_LEVE_idx` (`translation`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_names_translations_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`),
    CONSTRAINT `FK_items_atr_names_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Traducciones del atributo nombre';

CREATE TABLE IF NOT EXISTS `users` (
    `id_users` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_languages` int(11) unsigned NOT NULL,
    `password` char(128) NOT NULL,
    `user_name` char(128) NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `ip_register` varbinary(16) DEFAULT NULL,
    `ip_last_login` varbinary(16) DEFAULT NULL,
    `creation_date` datetime DEFAULT NULL,
    `email` varchar(254) DEFAULT NULL,
    `fb_user_id` varchar(255) DEFAULT NULL,
    `fb_access_token` varchar(255) DEFAULT NULL,
    `regen_password` varchar(254) DEFAULT NULL,
    `activation_token` varchar(254) DEFAULT NULL,
    `affiliated` varchar(30) NOT NULL DEFAULT 'no',
    `credit` decimal(10,2) NOT NULL DEFAULT '0.00',
    `status` enum('inactive','active','vacation','deleted') NOT NULL DEFAULT 'inactive',
    `news` tinyint(1) NOT NULL DEFAULT '1',
    `promotions` tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id_users`),
    UNIQUE KEY `email` (`email`),
    KEY `id_languages` (`id_languages`),
    FULLTEXT KEY `fb_user_id` (`fb_user_id`),
    FULLTEXT KEY `email2` (`email`),
    FULLTEXT KEY `user_name` (`user_name`),
    CONSTRAINT `FK_users_languages` FOREIGN KEY (`id_languages`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lista de usuarios en la web';

CREATE TABLE IF NOT EXISTS `users_coins_for_sale` (
    `id_users_coins_for_sale` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items` int(11) unsigned NOT NULL,
    `id_users` int(11) unsigned NOT NULL,
    `units` int(5) unsigned NOT NULL,
    `year` varchar(5) NOT NULL,
    `grade` enum('good','very_good','fine','very_fine','extra_fine','about_uncirculated','uncirculated','brilliant_uncirculated','proof') NOT NULL,
    `price` decimal(8,2) unsigned NOT NULL,
    `picture` varchar(500) DEFAULT NULL,
    `comment` varchar(500) DEFAULT NULL,
    `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_users_coins_for_sale`),
    UNIQUE KEY `id_users_coins_for_sale_unique` (`id_users_coins_for_sale`),
    KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    KEY `year` (`year`),
    KEY `grade` (`grade`),
    KEY `id_users_coins_for_sale` (`id_users_coins_for_sale`),
    CONSTRAINT `FK_users_coins_for_sale_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_users_coins_for_sale_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Body:
Too many tables.

Do not "normalize" simple things such as face_value and issues_on (and probably others.  It leads to extra JOINs without extra flexibility, etc.

I see a country id, but no country name??  It may be better to use this instead of the table and id:

country_code CHAR(2) CHARACTER SET ascii

and use the standard 2-letter country codes.  (It will also replace a 4-byte INT with a 2-byte string in various tables.

Also, since a PRIMARY KEY is, by definition (in MySQL), UNIQUE:

PRIMARY KEY(x)
UNIQUE     (x)  -- DROP this

Do you need LEFT?  That is, is the row in the 'right' table possibly missing?  If not, remove LEFT.

After you have cleaned up those, ask for another review.