Efficient Subqueries in MySQL

Question

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?

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

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,

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:04

Community

1
1

answered Dec 18 '12 at 03:35

John Woo

258,903
69
498
492

Beat me to it... only other optimization I was going to suggest was to select from C, join B, join A – Sam Dufel Dec 18 '12 at 03:37
Thanks a lot! This works great. One question though, how do I just return artist_profile? This query is returning rows from all three tables. – Colin Dec 18 '12 at 04:05
One other problem... Rows from artist_profile don't necessarily need to be linked to the other tables and I still want those to be returned... I've updated original query to show you what I mean. – Colin Dec 18 '12 at 04:11
see my updated query, by the way, can ypou giver sample records with desired result? – John Woo Dec 18 '12 at 05:09
Thanks for the help JW. I've updated my original question with desired results. – Colin Dec 18 '12 at 05:23
1

@Colin can you tell, what is missing here? [please click this link for demo](http://sqlfiddle.com/#!2/d2588/3) – John Woo Dec 18 '12 at 05:29

Efficient Subqueries in MySQL

1 Answers1