CODEWITHSUNDEEP
mysqlsqldatabaseperformance
Colin
Colin

Reputation: 2487

Efficient Subqueries in MySQL

I'm definitely not particularly skilled in MySQL, but until now I haven't had to be. I'm dealing with a large database and, in particularly, a user table with 1500+ rows. So I need to figure out how to efficiently accomplish what I had been accomplishing with the IN clause.

Here's the query:

SELECT * 
FROM artists_profile 
WHERE artist_name LIKE '%$test%' OR id IN (SELECT profile_id
              FROM artists_profileltp_join
            WHERE genre_id IN (SELECT id 
                               FROM artists_ltp 
                               WHERE genre LIKE '%$test%') OR  
                                     details LIKE '%$test%')

Database with sample data

    artists_profile           artists_profileltp_join
+------+-------------+     +---------+------------+---------+
|  ID  | artist_name |     |genre_id | profile_id | details |
+------+-------------+     +---------+------------+---------+
|   1  | Jake        |     |     1   |       2    | rap     |
|   2  | Obama       |     |     2   |       3    | smooth  |
|   3  | Bob         |     |     1   |       1    | metal   |
+------+-------------+     +---------+------------+---------+
    artists_ltp
+------+-------+
|  ID  | genre |
+------+-------+
|   1  | rock  |
|   2  | jazz  |
+------+-------+

Desired results for $test = "ja" would return artist_profile ID 1 and 3 because Jake starts with "ja" and Bob plays a genre that includes "ja".

The tables are pretty simple.

Artists_profile contains all of the unique information about a user. Artists_profileltp_join has profile_id (int(11)), genre_id (int(11)), and details (varchar(200)) fields and simply joins the artists_profile table to artists_ltp table.

The artists_ltp simply has a unique ID and varchar(50) field. It takes 30 seconds on average to run my query. What can I do to speed this up and make my subqueries more efficient?

Upvotes: 2

Views: 102

Answers (1)

John Woo
John Woo

Reputation: 263723

SELECT  DISTINCT a.*
FROM    artist_profile a
        INNER JOIN artists_profileltp b
            ON a.ID = b.profile_ID
        INNER JOIN artists_ltp  c
            ON b.genre_id = c.id
WHERE   c.genre LIKE '%$test%' OR
        c.details LIKE '%$test%'

JOIN would be better on this one. But unluckily, you're query will not use INDEX because you are using LIKE. I'll suggest that you read some article about FULL TEXT SEARCH

One more thing, the query is vulnerable with SQL Injection, please read the article below to learn how to prevent from it,

Upvotes: 3

Related Questions