问:
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>```

答:
I found the solution, getting the results in 0.125 / 0.180 seconds

The trick is to perform a subquery first with the attribute you want to order by, and the selected pagination, then use the item id in the rest of joins.

Example, selecting name as the attribute to order by:

```<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
    (SELECT
        i.id_items,
        GROUP_CONCAT(DISTINCT iant.translation ORDER BY iant.translation asc SEPARATOR ', ') AS names
    FROM
        items i
    INNER JOIN languages AS l ON l.language_code = "en"

    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

    GROUP BY id_items

    ORDER BY 2 asc

    LIMIT 0, 20) AS 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
</code>```

I hope this answer saves time to someone else.

Peace

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:
I found the solution, getting the results in 0.125 / 0.180 seconds

The trick is to perform a subquery first with the attribute you want to order by, and the selected pagination, then use the item id in the rest of joins.

Example, selecting name as the attribute to order by:

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
    (SELECT
        i.id_items,
        GROUP_CONCAT(DISTINCT iant.translation ORDER BY iant.translation asc SEPARATOR ', ') AS names
    FROM
        items i
    INNER JOIN languages AS l ON l.language_code = "en"

    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

    GROUP BY id_items

    ORDER BY 2 asc

    LIMIT 0, 20) AS 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

I hope this answer saves time to someone else.

Peace


来源: stackexchange

问:
what is the use of if else statements?
I don't quite understand the meaning of else if statements.

why not just to continue with the if statements in case one is false?

it works the same.

example with if only that will work the same with else if:

```<code>function testSize(num) {

  if (num < 5){

     return "Tiny";
  }

  if (num < 10){

    return "small";
  }

  return "Change Me";

}

testSize(7);
</code>```

答:
If all your if branches terminate the function (e.g., but returning a value of throwing an exception), you're right, and you really don't need an else statement (although some coding standards might recommend it).

However, if the if branches don't terminate the function, you'd have to use an else or else if clause to prevent multiple blocks from being executed. Assume, e.g., you want to log a message to the console instead of returning it:

```<code>if (num < 5) {
    console.log("Tiny");
} else if (num < 10) {
    console.log("small");
} else {
    console.log("Change Me");
}
</code>```

ContentLicense: CC BY-SA 3.0

Title: what is the use of if else statements?

Body:
I don't quite understand the meaning of else if statements.

why not just to continue with the if statements in case one is false?

it works the same.

example with if only that will work the same with else if:

function testSize(num) {

  if (num < 5){

     return "Tiny";
  }

  if (num < 10){

    return "small";
  }

  return "Change Me";

}

testSize(7);

Body:
If all your if branches terminate the function (e.g., but returning a value of throwing an exception), you're right, and you really don't need an else statement (although some coding standards might recommend it).

However, if the if branches don't terminate the function, you'd have to use an else or else if clause to prevent multiple blocks from being executed. Assume, e.g., you want to log a message to the console instead of returning it:

if (num < 5) {
    console.log("Tiny");
} else if (num < 10) {
    console.log("small");
} else {
    console.log("Change Me");
}


问:
Problems returning false after using XMLHttpRequest
I have found this Related entry and a few others but I have not quite found my answer so I am posting new. I am trying to run a database query from JavaScript using what I understand of Ajax. I need to return false if the email is already in the database and return true if it does not, but I cannot seem to figure out what I am doing wrong. I have posted my first attempt before I started messing around and making it messy.

JavaScript: It seems to return true no matter what.

```<code>function emailvalid(){

var email = document.getElementById('email').value;
var confirmemail = document.getElementById('cemail').value;
if(email != '' && confirmemail != '')
{
    if(email == '')
    {
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('tdemail').innerHTML = 'You must enter a valid email address.';
        document.getElementById('tdcemail').innerHTML = '';
        document.getElementById('cemail').value = '';
        return false;
    }

xmlhttp = new XMLHttpRequest();
var url="phpfiles/checkemail.php";
url = url+"?email="+email+"&cemail="+confirmemail;
xmlhttp.onreadystatechange = function()
{
    if (xmlhttp.readyState==4)
    {
        var answer = xmlhttp.responseText;
        if(answer == 'r0')
        {

        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('tdemail').innerHTML = 'This email address is already associated with an exiting account. Please login or use a different email address.';
        document.getElementById('tdcemail').innerHTML = '';
        document.getElementById('cemail').value = '';
        return false;
        }
        else if(answer == 'r1')
        {

        document.getElementById('tdemail').style.color = 'green';
        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').innerHTML = "Emails match and are valid";
        document.getElementById('tdcemail').style.color = 'green';
        document.getElementById('tdcemail').innerHTML = "Emails match and are valid";
        document.getElementById('cemail').style.backgroundColor ='green';
        return true;
        }
        else if(answer == 'r2')
        {

        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').innerHTML = "Email is not valid";
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = "Email is not valid.";
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
        else if(answer == 'r3')
        {

        document.getElementById('tdemail').style.color = 'green';
        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').innerHTML = "Email is valid";
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = "Emails do not match";
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
        else if(answer == 'r4')
        {

        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').innerHTML = 'Please enter a valid email address.';
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = 'Please enter a valid email address.';
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
    }
}
    xmlhttp.open("GET",url,true);
    xmlhttp.send(null);

}
else
{
    document.getElementById('tdemail').style.color = 'red';
    document.getElementById('email').style.backgroundColor ='red';
    document.getElementById('tdemail').innerHTML = "Email is not valid";
    document.getElementById('tdcemail').style.color = 'red';
    document.getElementById('tdcemail').innerHTML = "Email is not valid.";
    document.getElementById('cemail').style.backgroundColor ='red';
    return false;
}
}
</code>```

The innerHTML stuff works just fine which is also why I am confused.

PHP: works fine as far as I can tell.

```<code><?php
$email = $_GET['email'];
$cemail = $_GET['cemail'];
$emailvalidstring = "/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i";
include('connection.php');

$checkemailquery = "SELECT email FROM users WHERE email='".$email."'";
$checkemailresult = mysql_query($checkemailquery);

// if query failed.
if(!$checkemailresult)
{
    $error = mysql_error();
    print $error;
    exit;
}
//if the query did run, the email exists. Print error and exit.
if(mysql_affected_rows() != 0)
{
    echo "r0";
    return false;
}
else{
    if(preg_match($emailvalidstring, $email) && $email == $cemail)
    {
        echo "r1";
        return true;
    }
    else if(!preg_match($emailvalidstring, $email) && $email == $cemail)
    {
            echo "r2";
            return false;
    }
    else if(preg_match($emailvalidstring, $email) && $email != $cemail)
    {
        echo "r3";
        return false;
    }
    else if(!preg_match($emailvalidstring, $email) && $email != $cemail)
    {
        echo "r4";
        return false;
    }
}
?>
</code>```

html: I have it do a double check, once to alert the user if it is already taken, and then a second check on the server to make sure it is still true. cemail is the confirmation email.

```<code><form  id="newaccount" name="newaccount" method="post" action="phpfiles/accountcode.php" onSubmit="return emailvalid()" >
<input type="text" id="email" onBlur="emailvalid()"/>
<input type="text" id="cemail" onBlur="emailvalid()" />
<input type="submit" value="New"/>
</form>
</code>```

I hope I have laid it out well enough. I am open to ANY ideas as I am rather new at this.

答:
Not all code paths inside your emailvalid javascript function return a value. For example, after calling xmlhttp.send(null);, you're not returning anything.

Most of your return statements are actually returning from the anonymous function assigned to onreadystatechange, not from the emailvalid function, which I'm guessing is not what you want.

ContentLicense: CC BY-SA 4.0

Body:
I have found this Related entry and a few others but I have not quite found my answer so I am posting new. I am trying to run a database query from JavaScript using what I understand of Ajax. I need to return false if the email is already in the database and return true if it does not, but I cannot seem to figure out what I am doing wrong. I have posted my first attempt before I started messing around and making it messy.

JavaScript: It seems to return true no matter what.

function emailvalid(){

var email = document.getElementById('email').value;
var confirmemail = document.getElementById('cemail').value;
if(email != '' && confirmemail != '')
{
    if(email == '')
    {
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('tdemail').innerHTML = 'You must enter a valid email address.';
        document.getElementById('tdcemail').innerHTML = '';
        document.getElementById('cemail').value = '';
        return false;
    }

xmlhttp = new XMLHttpRequest();
var url="phpfiles/checkemail.php";
url = url+"?email="+email+"&cemail="+confirmemail;
xmlhttp.onreadystatechange = function()
{
    if (xmlhttp.readyState==4)
    {
        var answer = xmlhttp.responseText;
        if(answer == 'r0')
        {

        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('tdemail').innerHTML = 'This email address is already associated with an exiting account. Please login or use a different email address.';
        document.getElementById('tdcemail').innerHTML = '';
        document.getElementById('cemail').value = '';
        return false;
        }
        else if(answer == 'r1')
        {

        document.getElementById('tdemail').style.color = 'green';
        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').innerHTML = "Emails match and are valid";
        document.getElementById('tdcemail').style.color = 'green';
        document.getElementById('tdcemail').innerHTML = "Emails match and are valid";
        document.getElementById('cemail').style.backgroundColor ='green';
        return true;
        }
        else if(answer == 'r2')
        {

        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').innerHTML = "Email is not valid";
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = "Email is not valid.";
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
        else if(answer == 'r3')
        {

        document.getElementById('tdemail').style.color = 'green';
        document.getElementById('email').style.backgroundColor ='green';
        document.getElementById('tdemail').innerHTML = "Email is valid";
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = "Emails do not match";
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
        else if(answer == 'r4')
        {

        document.getElementById('tdemail').style.color = 'red';
        document.getElementById('email').style.backgroundColor ='red';
        document.getElementById('tdemail').innerHTML = 'Please enter a valid email address.';
        document.getElementById('tdcemail').style.color = 'red';
        document.getElementById('tdcemail').innerHTML = 'Please enter a valid email address.';
        document.getElementById('cemail').style.backgroundColor ='red';
        return false;
        }
    }
}
    xmlhttp.open("GET",url,true);
    xmlhttp.send(null);

}
else
{
    document.getElementById('tdemail').style.color = 'red';
    document.getElementById('email').style.backgroundColor ='red';
    document.getElementById('tdemail').innerHTML = "Email is not valid";
    document.getElementById('tdcemail').style.color = 'red';
    document.getElementById('tdcemail').innerHTML = "Email is not valid.";
    document.getElementById('cemail').style.backgroundColor ='red';
    return false;
}
}

The innerHTML stuff works just fine which is also why I am confused.

PHP: works fine as far as I can tell.

<?php
$email = $_GET['email'];
$cemail = $_GET['cemail'];
$emailvalidstring = "/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i";
include('connection.php');

$checkemailquery = "SELECT email FROM users WHERE email='".$email."'";
$checkemailresult = mysql_query($checkemailquery);

// if query failed.
if(!$checkemailresult)
{
    $error = mysql_error();
    print $error;
    exit;
}
//if the query did run, the email exists. Print error and exit.
if(mysql_affected_rows() != 0)
{
    echo "r0";
    return false;
}
else{
    if(preg_match($emailvalidstring, $email) && $email == $cemail)
    {
        echo "r1";
        return true;
    }
    else if(!preg_match($emailvalidstring, $email) && $email == $cemail)
    {
            echo "r2";
            return false;
    }
    else if(preg_match($emailvalidstring, $email) && $email != $cemail)
    {
        echo "r3";
        return false;
    }
    else if(!preg_match($emailvalidstring, $email) && $email != $cemail)
    {
        echo "r4";
        return false;
    }
}
?>

html: I have it do a double check, once to alert the user if it is already taken, and then a second check on the server to make sure it is still true. cemail is the confirmation email.

<form  id="newaccount" name="newaccount" method="post" action="phpfiles/accountcode.php" onSubmit="return emailvalid()" >
<input type="text" id="email" onBlur="emailvalid()"/>
<input type="text" id="cemail" onBlur="emailvalid()" />
<input type="submit" value="New"/>
</form>

I hope I have laid it out well enough. I am open to ANY ideas as I am rather new at this.

ContentLicense: CC BY-SA 2.5


问:
Display text from alt tag of from title tag on a button with CSS
How to display text from alt tag of from title tag on a button with CSS or with JavaScript?
Something like this

```<code>document.getElementById("myBtn").value="MyNewValue";
</code>```

But how do I get the ID's for this buttons?

The buttons are social login buttons and you can see them here:

Virtual Forms Framework login

答:
You have to add some javascript code to achieve this. Check below Snippet:

$('.the_champ_login_ul li').each(function(index) {
  $('' + $(this).find('i').attr('alt') + '').insertAfter($(this).find('i ss'));
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>.theChampLogin {
  width: 100%;
  display: block;
}

.theChampFacebookBackground {
  background-color: #3C589A;
}

.theChampFacebookLoginSvg {
  background: url(//login.create.net/images/icons/user/facebook_30x30.png) left no-repeat;
}

.theChampTwitterLoginSvg {
  background: url(//login.create.net/images/icons/user/twitter-b_30x30.png) left no-repeat;
}

.theChampLoginSvg {
  height: 100%;
  width: 35px;
  display: inline-block;
}

.theChampLogin {
  padding: 0!important;
  margin: 2px;
  height: 35px;
  float: left;
  cursor: pointer;
  border: none;
}

ul.the_champ_login_ul li i span {
  font-style: normal;
  display: inline-block;
  color: #fff;
  line-height: normal;
  vertical-align: top;
  padding-top: 10px;
}

ul.the_champ_login_ul {
  list-style: none!important;
  padding-left: 0!important;
}

.theChampTwitterBackground {
  background-color: #55acee;
}</code>```

    <ss class="theChampLoginSvg theChampFacebookLoginSvg"></ss>

    <ss class="theChampLoginSvg theChampTwitterLoginSvg"></ss>

```

Body:
How to display text from alt tag of from title tag on a button with CSS or with JavaScript?
Something like this

document.getElementById("myBtn").value="MyNewValue";

But how do I get the ID's for this buttons?

The buttons are social login buttons and you can see them here:

Virtual Forms Framework login

Body:
You have to add some javascript code to achieve this. Check below Snippet:

$('.the_champ_login_ul li').each(function(index) {
  $('<span>' + $(this).find('i').attr('alt') + '</span>').insertAfter($(this).find('i ss'));
});

.theChampLogin {
  width: 100%;
  display: block;
}

.theChampFacebookBackground {
  background-color: #3C589A;
}

.theChampFacebookLoginSvg {
  background: url(//login.create.net/images/icons/user/facebook_30x30.png) left no-repeat;
}

.theChampTwitterLoginSvg {
  background: url(//login.create.net/images/icons/user/twitter-b_30x30.png) left no-repeat;
}

.theChampLoginSvg {
  height: 100%;
  width: 35px;
  display: inline-block;
}

.theChampLogin {
  padding: 0!important;
  margin: 2px;
  height: 35px;
  float: left;
  cursor: pointer;
  border: none;
}

ul.the_champ_login_ul li i span {
  font-style: normal;
  display: inline-block;
  color: #fff;
  line-height: normal;
  vertical-align: top;
  padding-top: 10px;
}

ul.the_champ_login_ul {
  list-style: none!important;
  padding-left: 0!important;
}

.theChampTwitterBackground {
  background-color: #55acee;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<ul class="the_champ_login_ul">
  <li>
    <i class="theChampLogin theChampFacebookBackground theChampFacebookLogin" alt="Login with Facebook" title="Login with Facebook" style="display: block;"><ss class="theChampLoginSvg theChampFacebookLoginSvg"></ss></i></li>
  <li>
    <i class="theChampLogin theChampTwitterBackground theChampFacebookLogin" alt="Login with Twitter" title="Login with Twitter" style="display: block;"><ss class="theChampLoginSvg theChampTwitterLoginSvg"></ss></i></li>
</ul>


答:
Your code is only showing your belief. What would happen in the example below?

function testSize(num) {
  if (num < 5){
     x = 1;
  }
  if (num < 10){
    x = 2;
  }
  result = complex calculations;
}
function testSize2(num) {
  if (num < 5){
     x = 1;
  } else if (num < 10){
    x = 2;
  }
  return x * 2;
}
testSize(4); // result is 4
testSize2(4); // result is 2
```

x may also be involved in more calculations

Title: what is the use of if else statements?

Body:
Your code is only showing your belief. What would happen in the example below?

function testSize(num) {
  if (num < 5){
     x = 1;
  }
  if (num < 10){
    x = 2;
  }
  result = complex calculations;
}
function testSize2(num) {
  if (num < 5){
     x = 1;
  } else if (num < 10){
    x = 2;
  }
  return x * 2;
}
testSize(4); // result is 4
testSize2(4); // result is 2

x may also be involved in more calculations


问:
How to disable drag option if option is not chosen from dropdown?
How to disable drag option from "external-events-listing"  if option is "choose client" in drop down list ? and alert to select option from drop down list?

This the complete code

Thanks in advance

```<code><select id="client-list" onchange="changeImage(this)" style="width:150px; height:40px; margin-left:18px;margin-top:2px;">
<option value="Choose Client">---Choose Client---</option>
<option value="Client 1">Client 1</option>
<option value="Client 2">Client 2</option>
<option value="Client 3">Client 3</option>
<option value="Client 4">Client 4</option>
<option value="Client 5">Client 5</option>
</select>

    <div id='external-events'>
        <div id='external-events-listing'>
            <h4>Channel List</h4>
            <div class='fc-event'>My Event 1</div>
            <div class='fc-event'>My Event 2</div>
            <div class='fc-event'>My Event 3</div>
            <div class='fc-event'>My Event 4</div>
            <div class='fc-event'>My Event 5</div>
        </div>
    </div>
</code>```

答:
```<code>$('#calendar').fullCalendar({
  // ...
  allDaySlot: false,
  // ...
});

$("#client-list").on("change", function() {
  // ...
  $('#external-events .fc-event').each(function() {
    if ($("#client-list").val() !== "Choose Client") {
      $(this).draggable({
        zIndex: 999,
        revert: true,
        revertDuration: 0
      });
    } else {
      $(this).draggable('disable');
    }
  });
  // ...
});

$('#client-list').trigger('change');
</code>```

```<code>if (selectedValue !== "Choose Client") {
 $(this).draggable('enable');
} else {
 $(this).draggable('disable');
}
</code>```

If selected is not 'Choose Client' then all events are draggable.

$(document).ready(function() {
  /* initialize the external events
  -----------------------------------------------------------------*/

  $('#external-events .fc-event').each(function() {

    // store data so the calendar knows to render an event upon drop
    $(this).data('event', {
      title: $.trim($(this).text()), // use the element's text as the event title
      stick: true // maintain when user navigates (see docs on the renderEvent method)
    });

    // make the event draggable using jQuery UI
    $(this).draggable({
      zIndex: 999,
      revert: true, // will cause the event to go back to its
      revertDuration: 0 //  original position after the drag
    });

  });

  /* initialize the calendar
  -----------------------------------------------------------------*/

  $('#calendar').fullCalendar({
    header: {
      left: 'prev,next today',
      center: 'title',
      right: 'month,agendaWeek,agendaDay'
    },
    defaultView: 'agendaDay',
    unselectAuto: false,
    selectable: true,
    allDaySlot: false,
    selectHelper: false,
    editable: true,
    droppable: true, // this allows things to be dropped onto the calendar
    drop: function() {},
    eventRender: function(event, element) {
      element.find(".fc-content").append("x");
    },
    eventResize: function(event, jsEvent, ui, view) {
      console.log("event", event);
    },
  });
});

// remove event on click in calender
$(document).on('click', '.close', function() {

  /* ------------------------------ */
  // debugger; // I commented out
  /* ------------------------------ */

  var id = $(this).data('id');
  $('#calendar').fullCalendar('removeEvents', id);
  $(this).parent().remove();
});

$("#btnReset").click(function() {
  $('#calendar').fullCalendar('removeEvents');
});
$(document).ready(function() {
  $("#client-list").on("change", function() {
    var selectedValue = $(this).val();

    /* ------------------------------ */
    // location.reload(); // I commented out
    /* ------------------------------ */

    // IF #client-list value is not 'Choose Client' then all are draggable. ELSE none are draggable.
    /* ------------------------------ */
    $('#external-events .fc-event').each(function() {
      if (selectedValue !== "Choose Client") {
        $(this).draggable('enable');
      } else {
        $(this).draggable('disable');
      }
    });
    /* ------------------------------ */

  });

  // Trigger change on load, to check the current #client-list value and 'enable' / 'disable' draggable to items.
  /* ------------------------------ */
  $('#client-list').trigger('change');
  /* ------------------------------ */

});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>button.remove {
  font-size: .85em;
  border: 1px solid #3a87ad;
  background-color: #3a87ad;
  font-weight: 400;
  color: #fff;
  margin: 3px 0;
}

calender-body {
  margin-top: 40px;
  text-align: center;
  font-size: 14px;
  font-family: "Lucida Grande", Helvetica, Arial, Verdana, sans-serif;
}

.fc-content span.close {
  color: #fff;
  opacity: 1;
  text-shadow: none;
  font-size: 16px;
}

#wrap {
  width: 1500px;
  margin: 0 auto;
}

#external-events {
  float: left;
  width: 150px;
  padding: 0 10px;
  border: 1px solid #ccc;
  background: #eee;
  text-align: left;
}

#external-events h4 {
  font-size: 16px;
  margin-top: 0;
  padding-top: 1em;
}

#external-events .fc-event {
  margin: 10px 0;
  cursor: pointer;
}

#external-events p {
  margin: 1.5em 0;
  font-size: 11px;
  color: #666;
}

#external-events p input {
  margin: 0;
  vertical-align: middle;
}

#calendar {
  float: left;
  width: 1150px;
}

.mb-20 {
  margin-bottom: 20px;
}

.as-console-wrapper {
  z-index: 32;
}</code>```

<link rel='stylesheet prefetch' href='https://cdnjs.cloudflare.com/ajax/libs/fullcalendar/2.2.7/fullcalendar.min.css'>
<link rel='stylesheet prefetch' href='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css'>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

    <select id="client-list" onchange="/* changeImage(this) */" style="width:150px; height:40px; margin-left:18px;margin-top:2px;">
<option value="Choose Client">---Choose Client---</option>
<option value="Client 1">Client 1</option>
<option value="Client 2">Client 2</option>
<option value="Client 3">Client 3</option>
<option value="Client 4">Client 4</option>
<option value="Client 5">Client 5</option>
</select>

Channel List

My Event 1

My Event 2

My Event 3

My Event 4

My Event 5

      Reset Calender

```

Body:
How to disable drag option from "external-events-listing"  if option is "choose client" in drop down list ? and alert to select option from drop down list?

This the complete code

Thanks in advance

<select id="client-list" onchange="changeImage(this)" style="width:150px; height:40px; margin-left:18px;margin-top:2px;">
<option value="Choose Client">---Choose Client---</option>
<option value="Client 1">Client 1</option>
<option value="Client 2">Client 2</option>
<option value="Client 3">Client 3</option>
<option value="Client 4">Client 4</option>
<option value="Client 5">Client 5</option>
</select>

Channel List

My Event 1

My Event 2

My Event 3

My Event 4

My Event 5

Body:
$('#calendar').fullCalendar({
  // ...
  allDaySlot: false,
  // ...
});

$("#client-list").on("change", function() {
  // ...
  $('#external-events .fc-event').each(function() {
    if ($("#client-list").val() !== "Choose Client") {
      $(this).draggable({
        zIndex: 999,
        revert: true,
        revertDuration: 0
      });
    } else {
      $(this).draggable('disable');
    }
  });
  // ...
});

$('#client-list').trigger('change');

if (selectedValue !== "Choose Client") {
 $(this).draggable('enable');
} else {
 $(this).draggable('disable');
}

If selected is not 'Choose Client' then all events are draggable.

$(document).ready(function() {
  /* initialize the external events
  -----------------------------------------------------------------*/

  $('#external-events .fc-event').each(function() {

    // store data so the calendar knows to render an event upon drop
    $(this).data('event', {
      title: $.trim($(this).text()), // use the element's text as the event title
      stick: true // maintain when user navigates (see docs on the renderEvent method)
    });

    // make the event draggable using jQuery UI
    $(this).draggable({
      zIndex: 999,
      revert: true, // will cause the event to go back to its
      revertDuration: 0 //  original position after the drag
    });

  });

  /* initialize the calendar
  -----------------------------------------------------------------*/

  $('#calendar').fullCalendar({
    header: {
      left: 'prev,next today',
      center: 'title',
      right: 'month,agendaWeek,agendaDay'
    },
    defaultView: 'agendaDay',
    unselectAuto: false,
    selectable: true,
    allDaySlot: false,
    selectHelper: false,
    editable: true,
    droppable: true, // this allows things to be dropped onto the calendar
    drop: function() {},
    eventRender: function(event, element) {
      element.find(".fc-content").append("<span class='close' data-id='" + event._id + "'>x</span>");
    },
    eventResize: function(event, jsEvent, ui, view) {
      console.log("event", event);
    },
  });
});

// remove event on click in calender
$(document).on('click', '.close', function() {

  /* ------------------------------ */
  // debugger; // I commented out
  /* ------------------------------ */

  var id = $(this).data('id');
  $('#calendar').fullCalendar('removeEvents', id);
  $(this).parent().remove();
});

$("#btnReset").click(function() {
  $('#calendar').fullCalendar('removeEvents');
});
$(document).ready(function() {
  $("#client-list").on("change", function() {
    var selectedValue = $(this).val();

    /* ------------------------------ */
    // location.reload(); // I commented out
    /* ------------------------------ */

    // IF #client-list value is not 'Choose Client' then all are draggable. ELSE none are draggable.
    /* ------------------------------ */
    $('#external-events .fc-event').each(function() {
      if (selectedValue !== "Choose Client") {
        $(this).draggable('enable');
      } else {
        $(this).draggable('disable');
      }
    });
    /* ------------------------------ */

  });

  // Trigger change on load, to check the current #client-list value and 'enable' / 'disable' draggable to items.
  /* ------------------------------ */
  $('#client-list').trigger('change');
  /* ------------------------------ */

});

button.remove {
  font-size: .85em;
  border: 1px solid #3a87ad;
  background-color: #3a87ad;
  font-weight: 400;
  color: #fff;
  margin: 3px 0;
}

calender-body {
  margin-top: 40px;
  text-align: center;
  font-size: 14px;
  font-family: "Lucida Grande", Helvetica, Arial, Verdana, sans-serif;
}

.fc-content span.close {
  color: #fff;
  opacity: 1;
  text-shadow: none;
  font-size: 16px;
}

#wrap {
  width: 1500px;
  margin: 0 auto;
}

#external-events {
  float: left;
  width: 150px;
  padding: 0 10px;
  border: 1px solid #ccc;
  background: #eee;
  text-align: left;
}

#external-events h4 {
  font-size: 16px;
  margin-top: 0;
  padding-top: 1em;
}

#external-events .fc-event {
  margin: 10px 0;
  cursor: pointer;
}

#external-events p {
  margin: 1.5em 0;
  font-size: 11px;
  color: #666;
}

#external-events p input {
  margin: 0;
  vertical-align: middle;
}

#calendar {
  float: left;
  width: 1150px;
}

.mb-20 {
  margin-bottom: 20px;
}

.as-console-wrapper {
  z-index: 32;
}

<link rel='stylesheet prefetch' href='https://cdnjs.cloudflare.com/ajax/libs/fullcalendar/2.2.7/fullcalendar.min.css'>
<link rel='stylesheet prefetch' href='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css'>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

<!--scripts-->
<script src='https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.9.0/moment.min.js'></script>
<script src='https://code.jquery.com/jquery-1.11.2.min.js'></script>
<script src='https://code.jquery.com/ui/1.11.2/jquery-ui.min.js'></script>
<script src='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/fullcalendar/2.2.7/fullcalendar.min.js'></script>

<div id='wrap'>
  <img src="" alt="client logo" id='client-logo' height=80 width=80 style="float: left;margin-left:45px;">
  <div class="clients" style="float:left;clear:left;">
    <select id="client-list" onchange="/* changeImage(this) */" style="width:150px; height:40px; margin-left:18px;margin-top:2px;">
<option value="Choose Client">---Choose Client---</option>
<option value="Client 1">Client 1</option>
<option value="Client 2">Client 2</option>
<option value="Client 3">Client 3</option>
<option value="Client 4">Client 4</option>
<option value="Client 5">Client 5</option>
</select>

    <div id='external-events'>
      <div id='external-events-listing'>
        <h4>Channel List</h4>
        <div class='fc-event'>My Event 1</div>
        <div class='fc-event'>My Event 2</div>
        <div class='fc-event'>My Event 3</div>
        <div class='fc-event'>My Event 4</div>
        <div class='fc-event'>My Event 5</div>
      </div>
    </div>
    <div class="container">
      <span type="reset" id="btnReset" class="btn btn-default" style="margin
    top: 10px;">Reset Calender</span>
      <ul id="daysEvent"></ul>
      <div id='calendar'></div>
    </div>
  </div>


问:
Why do you have to specify a type for pointers?
Why do you have to set a type for pointers? Aren’t they just a placeholder for addresses and all those addresses? Therefore, won't all pointers no matter what type specified occupy an equal size of memory?</p>

答:
Different types take up different amounts of memory. So when advancing a pointer (e.g. in an array), we need to take the type's size into account.
For example, because a char takes up only one byte, going to the next element means adding 0x01 to the address. But because a int takes up 4 bytes (on many architectures), getting to the next element requires adding 0x04 to the address stored in the pointer.
Now, we could have a single pointer type which simply describes an address without type information (in fact, this is what void* is for), but then every time we wanted to increment or decrement it, we'd need to give the type's size as well.

Here's some real C code which demonstrates the pains you'd go through:

```<code>#include <stdlib.h>
typedef void* pointer;
int main(void) {
    pointer numbers = calloc(10, sizeof(int));
    int i;
    for (i = 0; i < 10; i++)
        *(int*)(numbers + i * sizeof(int)) = i;
        /* this could have been simply "numbers[i] = i;" */
    /* ... */
    return 0;
}
</code>```

Three important things to notice here:

We have to multiply the index by sizeof(int) every time; adding
simply i will not do: the first iteration would correctly access the
first 4-byte integer, but the second iteration would look for the
integer which starts with the second byte of the first integer, the
third would start with the third byte of the first integer, and so
on. It's very unlikely that this is desirable!

The compiler needs to know how much information it can store in a
pointer when assigning to the address it points to. For example, if
you try to store a number greater than 2^8 in a char, the compiler
should know to truncate the number and not overwrite the next few
bytes of memory, which might extend into the next page (causing a
segmentation fault) or, worse, be used to store other data in your
program, resulting in a subtle bug.

Speaking of width, we know in our program above that numbers stores
ints -- what if we didn't? What if, for example, we tried to store an
int in the address pointed to an array of a larger data type (on some
architectures), like a long? Then our generic functions would end up
having to compare the widths of both types, probably using the
minimum of the two, and then if the type being stored is smaller than
its container you start having to worry about endianness to make sure
you align the value being stored with the correct end of the
container.

Body:
Different types take up different amounts of memory. So when advancing a pointer (e.g. in an array), we need to take the type's size into account.
For example, because a char takes up only one byte, going to the next element means adding 0x01 to the address. But because a int takes up 4 bytes (on many architectures), getting to the next element requires adding 0x04 to the address stored in the pointer.
Now, we could have a single pointer type which simply describes an address without type information (in fact, this is what void* is for), but then every time we wanted to increment or decrement it, we'd need to give the type's size as well.

Here's some real C code which demonstrates the pains you'd go through:

#include <stdlib.h>
typedef void* pointer;
int main(void) {
    pointer numbers = calloc(10, sizeof(int));
    int i;
    for (i = 0; i < 10; i++)
        *(int*)(numbers + i * sizeof(int)) = i;
        /* this could have been simply "numbers[i] = i;" */
    /* ... */
    return 0;
}

Three important things to notice here:

We have to multiply the index by sizeof(int) every time; adding
simply i will not do: the first iteration would correctly access the
first 4-byte integer, but the second iteration would look for the
integer which starts with the second byte of the first integer, the
third would start with the third byte of the first integer, and so
on. It's very unlikely that this is desirable!

The compiler needs to know how much information it can store in a
pointer when assigning to the address it points to. For example, if
you try to store a number greater than 2^8 in a char, the compiler
should know to truncate the number and not overwrite the next few
bytes of memory, which might extend into the next page (causing a
segmentation fault) or, worse, be used to store other data in your
program, resulting in a subtle bug.

Speaking of width, we know in our program above that numbers stores
ints -- what if we didn't? What if, for example, we tried to store an
int in the address pointed to an array of a larger data type (on some
architectures), like a long? Then our generic functions would end up
having to compare the widths of both types, probably using the
minimum of the two, and then if the type being stored is smaller than
its container you start having to worry about endianness to make sure
you align the value being stored with the correct end of the
container.


问:
SQL 2005 - quick way to quickly duplicate a database + data
I have my development database, I want to spin off a duplicate for testing.  I want to do this often.

What's the fastest and easiest way to do this often?  I can get the job done by generating scripts w/ data included but I feel like there must be a better way.  Should I back up database 1 then screw around with it so I can restore it as a different name?

答:
In SQL 2005, I would say the fastest way is to use Copy Database, under Database --> Tasks

The source and destination can be the same, and it allows you to rename the database if desired.

OwnerDisplayName: BPAndrew

OwnerDisplayName: cmsjr


问:
How do you download a website?
Search engine bots crawl the web and download each page they go to for analysis, right?

How exactly do they download a page? What way do they store the pages?

I am asking because I want to run an analysis on a few webpages. I could scrape the page by going to the address but wouldn't it make more sense to download the pages to my computer and work on them from there?

答:
Try HTTrack

About the way they do it:

The indexing starts from a designated starting point (an entrance if you prefer). From there, the spider follows recursively all hyperlinks until a given depth.

Search engine spiders work like this as well, but there are many crawling simultaneously and there are other factors that count. For example a newly created post here in SO will be picked up by google very fast, but an update at a low traffic web site will be picked up even days later.

Title: How do you download a website?


问:
How to trace built-in append/3 in SWI-Prolog?
I would like to trace built-in append/3 in SWI-Prolog, but am getting the result immediately and cannot see what is going on in the middle.

Is there anything I have to do to enable trace for append?

```<code> ?- trace .
true.

[trace]  ?- append([a,b,c], [[],[2,3], b], X).
X = [a, b, c, [], [2, 3], b].
</code>```

答:
In SWI-Prolog, the append/3 predicate is defined in the lists module, which contains the directive:

```<code>:- set_prolog_flag(generate_debug_info, false).
</code>```

This SWI-Prolog proprietary directive instructs the compiler to skip tracing of any predicate definition inside the module. If you comment out the directive in the lists.pl file in your SWI-Prolog installation, you should then be able to trace calls to the append/3 predicate or any other predicate defined in the module.

Body:
I would like to trace built-in append/3 in SWI-Prolog, but am getting the result immediately and cannot see what is going on in the middle.

Is there anything I have to do to enable trace for append?

 ?- trace .
true.

[trace]  ?- append([a,b,c], [[],[2,3], b], X).
X = [a, b, c, [], [2, 3], b].

Body:
In SWI-Prolog, the append/3 predicate is defined in the lists module, which contains the directive:

:- set_prolog_flag(generate_debug_info, false).

This SWI-Prolog proprietary directive instructs the compiler to skip tracing of any predicate definition inside the module. If you comment out the directive in the lists.pl file in your SWI-Prolog installation, you should then be able to trace calls to the append/3 predicate or any other predicate defined in the module.