StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEfficient Subqueries in MySQL
text
Body
copied!<p>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.</p> <p>Here's the query:</p> <pre><code>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%') </code></pre> <p>Database with sample data</p> <pre><code> 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 | +------+-------+ </code></pre> <p>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".</p> <p>The tables are pretty simple. </p> <p>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. </p> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload