wordpress ajax relationship query

Using the relationship field from Advanced Custom Fields I link artists to events. Both these artist and events are custom post types. For each event, the post IDs of the related artists are stored in an array as a custom meta field (lineup_artists).

On each event page I list all the artists. When you click on an artist, I’d like to show all the events where you can find this artist (through an AJAX call which shows the results in a bootstrap modal). I’ve tested the AJAX call and it’s working, but there’s something wrong with the query (takes very long to complete).

In my function I have:

$ArtistID = $_POST['ArtistID']; // Gets the ArtistID from the AJAX call

$meta_query = array(
    'key'       => 'lineup_artists',
    'value'     => '"' . $ArtistID .'"',
    'compare'   => 'LIKE'
);

    $args = array(
        'post_type'         => 'events',
        'meta_query'        => array($meta_query),
        'posts_per_page'    => 5,
        'post_status'       => 'publish',   
);

If I dump the results of wp_query, I get the following sql query:

SELECT SQL_CALC_FOUND_ROWS yt_posts.ID FROM yt_posts 

INNER JOIN yt_postmeta ON (yt_posts.ID = yt_postmeta.post_id)

WHERE 1=1 

AND yt_posts.post_type = 'events' 
AND (yt_posts.post_status = 'publish') 

AND ( (yt_postmeta.meta_key = 'lineup_artists' AND CAST(yt_postmeta.meta_value AS CHAR) LIKE '%"17497"%') ) 

GROUP BY yt_posts.ID ORDER BY yt_posts.post_date DESC LIMIT 0, 5

When I paste this query in phpmyadmin it takes very long to complete (I’ve never seen it finish because it takes so long).

Is this because the artist IDs are stored as an array? Someone told me that this is not very efficient and that these relations should be stored in a separate table (which I don’t know how to do). Is there something wrong with my query or is this a very inefficient way of querying relations?

EDIT: In reply to kaiser (let’s use ACDC as an example :-)):

In my event page I have this html/php for each artist (there can be 100+ artists on each event page. $artistID and $artistName are generated in the foreach loop.):

<a class="yt-artist" data-target="#modalArtist" data-toggle="modal" id="ArtistAttr" data-id="<?php echo $artistID; ?>" data-name="<?php echo $artistName ?>"><?php echo $artistName; ?></a>

This code is generated in a foreach loop to get all the artists:

$lineup = get_sub_field('lineup_artists'); // The relationship artists are in a repeater field
if($lineup):
foreach($lineup as $artist):
<a class=... etc

which will result in:

<a class="yt-artist" data-target="#modalArtist" data-toggle="modal" id="ArtistAttr" data-id="17497" data-name="ACDC">ACDC</a>

So clicking an artist shows the modal, and when the modal is shown the AJAX js is fired:

jQuery(document).ready(function() 
{
    $(document).on('click','.yt-artist',function()
    {
        var ArtistID = $(this).data('id');
            var ArtistName = $(this).data('name')
        $('#modalArtist').on('shown',function()
        { 
            jQuery.ajax(
            {  
                type: 'POST',  
                url: 'http://xxx/wp-admin/admin-ajax.php',  
                data: 
                {  
                    action: 'yt_ajax_artist_events_eventpage',  
                    ArtistID: ArtistID,
                                    ArtistName: ArtistName,
                },
                success: function(data, textStatus, XMLHttpRequest)
                {  
                    jQuery('#modalArtist').html(data);  
                },  
                error: function(MLHttpRequest, textStatus, errorThrown)
                {  
                alert(errorThrown);
                }
            });
        });

});

And the function (includes some HTML to inject in the modal):

function yt_ajax_artist_events_eventpage(){

    $ArtistID = $_POST['ArtistID'];
    $ArtistName = $_POST['ArtistName'];

    $meta_query = array(
        'key'       => 'lineup_artists',
        'value'     => $ArtistID,
        'compare'   => 'LIKE'
    );

    $args = array(
        'post_type'         => 'festivals',
        'meta_query'        => array($meta_query),
        'posts_per_page'    => 5,
        'post_status'       => 'publish',   
    );

    $results = '
    <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
        <h2>'.$ArtistName.'</h2>
    </div>
    <div class="modal-body">';
    $loop = new WP_Query($args);
    while ($loop->have_posts()) : $loop->the_post();

    $results .= '<h3>'.get_the_title().'</h3>';

    endwhile;
    wp_reset_query();
    $results .= '</div>';
    die($results);
}  
add_action( 'wp_ajax_nopriv_yt_ajax_artist_events_eventpage', 'yt_ajax_artist_events_eventpage' );  
add_action( 'wp_ajax_yt_ajax_artist_events_eventpage', 'yt_ajax_artist_events_eventpage' );