CODEWITHSUNDEEP
mysqlperformanceselectconcatenation
vegito15
vegito15

Reputation: 35

Mysql - query performance

I haven't data in my mysql table, so I can't check which sql statement will be faster.

I have table with colums: ID | user1 | user2 | table will have about 1 million records and I need to select id_user (int) from column user1 or user2.

First query: 

SELECT `id`, `data` FROM `table` WHERE `user1` = :id_user OR `user2` = :id_user

or second query:

SELECT `id`, `data` FROM `table` WHERE concat(user1, '/' user2) LIKE '%:id_user%'

Which query will select data faster?

Upvotes: 0

Views: 112

Answers (4)

Rick James
Rick James

Reputation: 142298

As already pointed out; neither of your queries will be efficient. Here is a workaround:

( SELECT `id`, `data` FROM `table` WHERE `user1` = :id_user )
UNION DISTINCT
( SELECT `id`, `data` FROM `table` WHERE `user2` = :id_user )

and have these indexes:

INDEX(user1)
INDEX(user2)

That way, the processing will very quickly perform each SELECT, then combine them using UNION.

Note, further, if there will be no overlaps (or you don't care), it is faster to use UNION ALL. Saying UNION DISTINCT dedups the result of the combined selects.

More on indexing.

Upvotes: 0

gview
gview

Reputation: 15361

Any time you apply a function on one or more columns of the table and use that in the WHERE clause, no index can be used. In order to get the result, the table will be scanned top to bottom.

You can think of this as a "computed" column. In order to evaluate the comparison, the DB must "compute" the column, so it is no longer a static column that exists in the database, but more akin to a variable. In your case, you are combining 2 columns and creating a pseudo column. For a computed column no index exists, so the database must read the row data, do the computation and then evaluate the comparison. If you look at the queries using EXPLAIN you will see that this query table scans all 1 million rows.

As per the comment, you should have an index on user1 and an index on user2, and the query will be highly performant.

I don't know the application but the implementation with a column name ('user') and an iteration (user1, user2) typically indicates a repeating group that should have been normalized into a many-to-many relationship.

With a structure like that in place you would not have a query with WHERE user1 = something OR user2 = something.

Upvotes: 4

Steffen Mächtel
Steffen Mächtel

Reputation: 1021

You can prepand "EXPLAIN" in front of your queries and get more informations about your queries. Even when you have a local database with a low amount of example entries inside your table. It will give some informations about used indexes/keys.

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

EXPLAIN SELECT `id`, `data` FROM `table` WHERE `user1` = :id_user OR `user2` = :id_user

EXPLAIN SELECT `id`, `data` FROM `table` WHERE concat(user1, '/' user2) LIKE '%:id_user%'

Upvotes: 1

Crutch Master
Crutch Master

Reputation: 189

First query faster, of course. Cast id to string, concat, search by string (not index) with "like". I don't know what may be slower that second query.

Upvotes: 0

Related Questions