CODEWITHSUNDEEP
mysqlmariadb
Mihir
Mihir

Reputation: 1176

Searching in all the tables of a mysql database

I have a mysql (mariadb) database with numerous tables and all the tables have the same structure.

For the sake of simplicity, let's assume the structure is as below.

UserID - Varchar (primary)
Email - Varchar (indexed)

Is it possible to query all the tables together for the Email field?

Edit: I have not finalized the db design yet, I could put all the data in single table. But I am afraid that large table will slow down the operations, and if it crashes, it will be painful to restore. Thoughts?

I have read some answers that suggested dumping all data together in a temporary table, but that is not an option for me.

Mysql workbench or PHPMyAdmin is not useful either, I am looking for a SQL query, not a frontend search technique.

Upvotes: 1

Views: 1139

Answers (2)

Psaicongru
Psaicongru

Reputation: 1

There is no generic SQL query to search for a specific value throughout the entire database.

A query of this type:

SELECT a, b, c FROM <<<all tables>>> WHERE b LIKE 'any%'

If you know all the table names in advance, you can write a query like this:

SELECT a, b, c FROM table1 WHERE b LIKE 'any%'
UNION ALL
SELECT a, b, c FROM table2 WHERE b LIKE 'any%'
UNION ALL
SELECT a, b, c FROM table3 WHERE b LIKE 'any%'
UNION ALL
SELECT a, b, c FROM table4 WHERE b LIKE 'any%'
...

Alternatively, you can create a view like this:

CREATE VIEW everything AS 
SELECT * FROM table1 
UNION ALL
SELECT * FROM table2 
UNION ALL
SELECT * FROM table3 
UNION ALL
SELECT * FROM table4 
...

Then use:

SELECT a, b, c FROM everything WHERE b LIKE 'any%'

If you don't know the names of all the tables in advance, you can retrieve them from the MySQL information_schema and write a query that returns all the tables.

Here you can find a git page with a guide on how to extract and execute the query you need.

Link: MySQL-Search-All-Tables

Upvotes: 0

O. Jones
O. Jones

Reputation: 108676

There's no concise way in SQL to say this sort of thing.

SELECT a,b,c  FROM <<<all tables>>> WHERE b LIKE 'whatever%'

If you know all your table names in advance, you can write a query like this.

SELECT a,b,c  FROM table1 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table2 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table3 WHERE b LIKE 'whatever%' 
UNION ALL
SELECT a,b,c  FROM table4 WHERE b LIKE 'whatever%' 
...

Or you can create a view like this.

CREATE VIEW everything AS 
SELECT *  FROM table1 
UNION ALL
SELECT *  FROM table2 
UNION ALL
SELECT *  FROM table3 
UNION ALL
SELECT *  FROM table4 
...

Then use 

SELECT a,b,c  FROM everything WHERE b LIKE 'whatever%'

If you don't know the names of all the tables in advance, you can retrieve them from MySQL's information_schema and write a program to create a query like one of my suggestion. If you decide to do that and need help, please ask another question.

These sorts of queries will, unfortunately, always be significantly slower than querying just one table. Why? MySQL must repeat the overhead of running the query on each table, and a single index is faster to use than multiple indexes on different tables.

Pro tip Try to design your databases so you don't add tables when you add users (or customers or whatever).

Edit You may be tempted to use multiple tables for query-performance reasons. With respect, please don't do that. Correct indexing will almost always give you better query performance than searching multiple tables. For what it's worth, a "huge" table for MySQL, one which challenges its capabilities, usually has at least a hundred million rows. Truly. Hundreds of thousands of rows are in its performance sweet spot, as long as they're indexed correctly. Here's a good reference about that, one of many. https://use-the-index-luke.com/

Another reason to avoid a design where you routinely create new tables in production: It's a pain in the ***xxx neck to maintain and optimize databases with large numbers of tables. Six months from now, as your database scales up, you'll almost certainly need to add indexes to help speed up some slow queries. If you have to add many indexes, you, or your successor, won't like it.

You may also be tempted to use multiple tables to make your database more resilient to crashes. With respect, it doesn't work that way. Crashes are rare, and catastrophic unrecoverable crashes are vanishingly rare on reliable hardware. And crashes can corrupt multiple tables. (Crash resilience: decent backups).

Keep in mind that MySQL has been in development for over a quarter-century (as have the other RDBMSs). Thousands of programmer years have gone into making it fast and resilient. You may as well leverage all that work, because you can't outsmart it. I know this because I've tried and failed.

Keep your database simple. Spend your time (your only irreplaceable asset) making your application excellent so you actually get millions of users.

Upvotes: 2

Related Questions