CiviCRM Community Forums (archive)

I'm doing some very simple mysql db queries using external identifier, such as

SELECT *
FROM  `civicrm_contact` contact
WHERE contact.external_identifier =123456

but they seem to be taking a really long time - anywhere between 3 and 6 seconds per query. If instead of external_identifier I use contact.id then the query time drops to 0.004s.

It is a problem I need to solve, as we regularly import external data coming from a third party based on external_identifier. Importing close to 1 million records when it takes 3 to 6 seconds per query, makes the import process impractical.

I know that contact.id is a Primary Key, while external_identifier is set up as a Unique Index, but the speed shouldn't be as slow as it is.

Brian

If you select * it probably won't use the index.

Try just selecting what you need, or select id, then select * where id=

I've tried that and it improves it somewhat, but not by much. Still in the 2s range.

I have tried changing external_identifier to type INT instead of VARCHAR and that has dropped it to 0.006s. What would the broader implications of that change be? All of our external_identifiers will be INT.

Things will probably seem fine ... until your next CiviCRM upgrade

At that point, if CiviCRM makes any upgrade references to the column you've altered, the upgrade process will probably fail hard. CiviCRM's DB update processes expect the DB to be in the exact state they created it (and built as they are as SQL generated from .tpl, it's not really possible for them to check column types or indexes, or to catch errors).

Changing the column type is probably minor and low-risk, but fair warning ...

If you select * it probably won't use the index.

Try just selecting what you need, or select id, then select * where id=

Are you sure about that? I've never heard that MySQL uses the SELECTed columns to determine which indexes it will use ... can you give any reference for that behaviour?

EXPLAIN shows the same results for the query with * and with the columns listed explicitly -

mysql> explain select id, contact_type,  contact_sub_type,  do_not_email,  do_not_phone,  do_not_mail,  do_not_sms,  do_not_trade,  is_opt_out,  legal_identifier,  external_identifier,  sort_name,  display_name,  nick_name,  legal_name,  image_URL,  preferred_communication_method,  preferred_language,  preferred_mail_format,  hash,  api_key,  source,  first_name,  middle_name,  last_name,  prefix_id,  suffix_id,  email_greeting_id,  email_greeting_custom,  email_greeting_display,  postal_greeting_id,  postal_greeting_custom,  postal_greeting_display,  addressee_id,  addressee_custom,  addressee_display,  job_title,  gender_id,  birth_date,  is_deceased,  deceased_date,  household_name,  primary_contact_id,  organization_name,  sic_code,  user_unique_id,  employer_id,  is_deleted from civicrm_contact where external_identifier=1;
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
| id | select_type | table           | type | possible_keys          | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | civicrm_contact | ALL  | UI_external_identifier | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from civicrm_contact where external_identifier=1;
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
| id | select_type | table           | type | possible_keys          | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | civicrm_contact | ALL  | UI_external_identifier | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-----------------+------+------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


Querying the table on a field that is a unique index should be instantaneous.

Could you explain the query to see if it is really using it?

Code: [Select]

explain select * from civicrm_contact where external_identifier='1';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

It doesn't look ok to me, but I have no idea what's the pb. SQL Guru to the rescue?

That "Extra" result is a false alarm - MySQL has checked the index and verified that no rows match the WHERE provided (you have no external_identifier=1) so it's internally added something like WHERE 0, which is the Impossible WHERE.

I do find it interesting though about the int vs. varchar. I'd expect that on a really old computer, but maybe worth investigating the use of indexes in civi more.

Also on really new computers. If an index is integer, the column type should be int. With very large datasets, this type of optimization becomes critical actually.

Trying to understand: So indeed, when querying one existing identifier, it seems to use the index, all good.

And you are saying that it's dog slow on your side but normal fast if you convert the column to an  int? Have you run a mysql tuner and checked if  the params for memory & co are ok?

mysql> explain select * from civicrm_contact where external_identifier="00132ed0388c91fdbbc333d0ba9cbed3";
+----+-------------+-----------------+-------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table           | type  | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+-----------------+-------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | civicrm_contact | const | UI_external_identifier | UI_external_identifier | 99      | const |    1 |       |
+----+-------------+-----------------+-------+------------------------+------------------------+---------+-------+------+-------+
1 row in set (0.00 sec)