1
\$\begingroup\$

I wrote a query that searches people based on search parameters from 3 other tables (phones, emails, addresses). I'm pretty sure this is not the most efficient way to do this, any performance tips are greatly appreciated :)

Note that the user may choose to search by less than all the possible criteria, for example search only by phone and email but not by address and name. In such cases a php code will dynamically generate a search query that excludes the unwanted parameters.

SQL

SELECT
  id,
  name,
  group_concat(
    DISTINCT concat( number, ' (', phones.description, ')' )
    ORDER BY phones.description
    SEPARATOR '<br>'
  ) AS phones,
  group_concat(
    DISTINCT concat( email, ' (', emails.description, ')' )
    ORDER BY emails.description
    SEPARATOR '<br>'
  ) AS emails,
  group_concat(
    DISTINCT concat( address, ' (', addresses.description, ')' )
    ORDER BY addresses.description
    SEPARATOR '<br>'
  ) AS addresses

FROM people
LEFT OUTER JOIN phones
  ON phones.person_id = id
LEFT OUTER JOIN emails
  ON emails.person_id = id
LEFT OUTER JOIN addresses
  ON addresses.person_id = id

WHERE id IN (
  SELECT DISTINCT person_id
  FROM phones
  WHERE number LIKE ?
  AND person_id IN (
    SELECT DISTINCT person_id
    FROM emails
    WHERE email LIKE ?
    AND person_id IN (
      SELECT DISTINCT person_id
      FROM addresses
      WHERE address LIKE ?
    )
  )
)
AND name LIKE ?
GROUP BY id
ORDER BY id DESC;

Sample database tables

people:
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | Bob                  |
|  2 | Daniel               |
|  3 | Joe                  |
|  4 | Some other name      |
|  5 | Robby Williams       |
+----+----------------------+

phones:
+-----------+------------+-------------+
| person_id | number     | description |
+-----------+------------+-------------+
|         1 | 123456789  | home        |
|         1 | 123412341  | office      |
|         2 | 1234554321 |             |
|         3 | 8525824725 | home        |
|         3 | 5832593952 | office      |
|         3 | 6035262953 | fax         |
|         3 | 6832525753 | office 2    |
|         3 | 6735926752 | z           |
|         3 | 6830589736 | zz          |
|         3 | 2893475979 | zzz         |
|         3 | 7823569459 | zzzz        |
|         4 | 666        | secretary   |
|         4 | 444422220  | office      |
|         4 | 111111111  | home        |
|         5 | 444422220  | office      |
|         5 | 1111111111 | home        |
+-----------+------------+-------------+

emails:
+-----------+------------------+-------------+
| person_id | email            | description |
+-----------+------------------+-------------+
|         1 | [email protected] |             |
|         2 | [email protected] | fake email  |
|         3 | [email protected]   |             |
+-----------+------------------+-------------+

addresses:
+-----------+----------------------------------------------------+-------------+
| person_id | address                                            | description |
+-----------+----------------------------------------------------+-------------+
|         1 | Anywhere                                           | home        |
|         1 | Nowhere, apt 2                                     | work        |
|         2 | The Moon                                           | home        |
|         2 | Venus                                              | office      |
|         3 | 123 something something, apt -2.5                  | basement    |
|         4 | Hello, apt 26                                      |             |
|         5 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | office      |
+-----------+----------------------------------------------------+-------------+

Sample output

For search parameters: [ '%1%', '%fakemail%', '%moon%', '%d%' ]

+----+--------+---------------+-------------------------------+-----------------------------------+
| id | name   | phones        | emails                        | addresses                         |
+----+--------+---------------+-------------------------------+-----------------------------------+
|  2 | Daniel | 1234554321 () | [email protected] (fake email) | The Moon (home)<br>Venus (office) |
+----+--------+---------------+-------------------------------+-----------------------------------+
\$\endgroup\$
3
  • \$\begingroup\$ You've created a very weird query. Does it actually do what you want? Talking about that: What kind of search do you really want to perform? Do all three parameters have to exactly match? Could one match? Do you want partial matches of parameters? It would even be better if you gave a reason for this, some context to this query. I also wonder what is in the description columns? What do your tables actually look like? The cherry on the pudding would be some example data... \$\endgroup\$ Commented Apr 27, 2019 at 15:08
  • \$\begingroup\$ @KIKOSoftware added examples of database and sample output. The parameters need to be a partial match (hence the LIKE operator). The user may choose to search by less than all of these parameters, in which case a php code will dynamically create a query string that excludes the unwanted search parameters. \$\endgroup\$ Commented Apr 27, 2019 at 16:43
  • \$\begingroup\$ As a joke I added some weird stuff to this database, but the description columns are supposed to contain stuff like "home", "office", "fax", etc. \$\endgroup\$ Commented Apr 27, 2019 at 16:53

1 Answer 1

Reset to default
2
\$\begingroup\$

Please note that on Code Review we actually review all of your code. So, even though you concentrated on one aspect; The most efficient query, I have to discuss more than that. I'll have to accept that you left out the PHP code, which seems to be an integral part of this query. I have work with what you have given.

The query

Let me first answer your question about the query. Yes, it can be more efficient if you loose the sub-queries. I will make this a two step process, which is easy when you're using PHP. In the first step you get the the id's of the persons that match the search: 

SELECT
  id
FROM people
JOIN phones
  ON phones.person_id = id AND
     phones.number LIKE ? 
JOIN emails
  ON emails.person_id = id AND
     emails.email LIKE ?
JOIN addresses
  ON addresses.person_id = id AND
     addresses.address LIKE ?
WHERE name LIKE ?
GROUP BY id
ORDER BY id DESC;

Then when you go through the results of this query you ask for the details of each person:

SELECT
  name,
  group_concat(
    DISTINCT concat(number, ' (', phones.description, ')')
    ORDER BY phones.description
    SEPARATOR '<br>'
  ) AS phones,
  group_concat(
    DISTINCT concat(email, ' (', emails.description, ')')
    ORDER BY emails.description
    SEPARATOR '<br>'
  ) AS emails,
  group_concat(
    DISTINCT concat(address, ' (', addresses.description, ')')
    ORDER BY addresses.description
    SEPARATOR '<br>'
  ) AS addresses
FROM people
JOIN phones ON phones.person_id = id 
JOIN emails ON emails.person_id = id 
JOIN addresses ON addresses.person_id = id 
WHERE id = ?;

Now you might think this is much worse than what you had. You had one query, and I have two of which one is repeated. This cannot possibly be better? Well, you're partially right. It is only better because it is simpler. It might even be faster. Why? Well, looking for single rows, with an indexed column, can be very fast. So the slower query is the first one, not the second one that is repeated. And my slow query is faster than yours because it doesn't have all the sub-queries. It is simpler. More over, the second query shouldn't be repeated that often. You don't want to present an user with hunderds of results, one page, with a maximum of about 10 to 20 results will do. If you really want to you could get these in one query with:

SELECT
  name,
  group_concat(
    DISTINCT concat(number, ' (', phones.description, ')')
    ORDER BY phones.description
    SEPARATOR '<br>'
  ) AS phones,
  group_concat(
    DISTINCT concat(email, ' (', emails.description, ')')
    ORDER BY emails.description
    SEPARATOR '<br>'
  ) AS emails,
  group_concat(
    DISTINCT concat(address, ' (', addresses.description, ')')
    ORDER BY addresses.description
    SEPARATOR '<br>'
  ) AS addresses
FROM people
JOIN phones ON phones.person_id = id 
JOIN emails ON emails.person_id = id 
JOIN addresses ON addresses.person_id = id 
WHERE FIND_IN_SET(id, ?);

Where FIND_IN_SET() contains the comma seperated id's of the persons you want to show on a page.

Search method

You seem to use separate search fields for the name, phone number, email and address tables. This might be useful, but I think most users would like a single search field to search through all the data in the database tables. This will become more obvious, the more fields you want to make searchable. Yes, it is nice to be able to search for a combination of a name and an email address, but often users don't need this. A single search field is easy to understand, and often what users expect. Check how Google let's you refine web searches.

Efficiency

The query above is looking through four tables. This is fine, unless those tables get very large, or you want to search through many more tables. One method of gaining more efficiency is by making a 'search summary' column in the people table. Whenever a detail of a person changes, the summary for that person should be updated, but that can be done quickly since it only involves one person. With such a summary you will only have to look in one column, in one table, to perform a search through the whole database. More advanced search algorithms exist, but I'll leave it at this. My point is, you have to think about the future of your database now. If there is even a slight possibility it will grow a lot, then you need to take that into account at the design stage.

Primary keys

The tables phones, emails and addresses are missing unique primary keys. You often need one, for instance to join tables, or for something as simple as being able to edit rows in PHPMyAdmin. I'm not saying you should have them, but in practice they are often handy.

Naming consistency

I notice that you use plurals for database table names. Most people will argue to use singulars. What I really have a problem with is a table called people and then a foreign key called person_id. Either call the table person or the key people_id. No matter what you prefer, you have to be consistent about it.

When I work with databases I find it often confusing when there are many tables with the same column names in it. I always try to choose very descriptive and unique names. So instead of number I would choose phoneNo, and instead of description I would use connectionType and phoneLocation. That way you would always know which column it is, and you would also not mix the connection type and phone location in one column. And if similary named columns, in different tables, really contain the same information then it is probably time to normalize the database.

Addresses

Hardly anybody stores a whole address in a single column. It is almost always useful to separate them into meaningful items. Before you know it, you will want to use an API that requires you to supply a ZIP code of a person. I got caught out once by an shipping API that insisted on a separate house number. I had to split the addresses of thousands of records, and believe me, that was no fun.

HTML in queries

I noticed that you have some HTML in your query. That is highly unusual. Such a query is part of the data management in your model, not part of the final output view to the user. See the MVC pattern. MVC pattern I know that patterns are no fun, but believe me, it is useful, in the long run, to separate your search query from the HTML output to the user. For instance, if you want to output the result of the search in anything else than HTML.

\$\endgroup\$
4
  • \$\begingroup\$ A quick comment before I read the whole review: the search query you propose doesn't select the entire contact information, only the (phones/emails/addresses) that match the search criteria. If I search for a person with email LIKE '%example%', I want the search results to include all of this person's emails even if not all of them contain the word 'example'. \$\endgroup\$ Commented Apr 27, 2019 at 19:50
  • \$\begingroup\$ Ah, oke, I didn't get that from the question. But it makes sense. My query doesn't do that. I'll have to rethink this. \$\endgroup\$ Commented Apr 27, 2019 at 19:58
  • \$\begingroup\$ Great review, taught me some important lessons, thank you very much! \$\endgroup\$ Commented Apr 27, 2019 at 20:12
  • \$\begingroup\$ I've updated the query bit. It is simply better not to try to do this in one query, especially since this is going to be part of PHP code. \$\endgroup\$ Commented Apr 27, 2019 at 20:17

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.