SQL query : Select a thumbnail for a media

Question

I'm doing a custom gallery for a portfolio page in a website and It works well if I simply use the URL of the media uploaded this way :

SELECT * FROM wp_posts AS p INNER JOIN wp_postmeta AS m ON p.ID=m.post_id 
WHERE p.post_type='attachment' AND m.meta_key='_wp_attached_file'

The thing is, if I upload too many big files, the page gets sloooow. This is why I'd like to use thumbnail but I have no idea how to select them accordingly in the database. They're created in the wp-content/uploads folder, but I can't see which meta_key to select in the database.

Also noticable, my databse doesn't have any "_thumbnail_id" in the meta_key column.

Anyone has an answer ?

Answer 1

$Featured_image = $wpdb->get_results("
    SELECT p.*
      FROM net_5_postmeta AS pm
     INNER JOIN net_5_posts AS p ON pm.meta_value=p.ID 
     WHERE pm.post_id = $da_id
       AND pm.meta_key = '_thumbnail_id' 
     ORDER BY p.post_date DESC 
     LIMIT 15
",'ARRAY_A');

A related solution, to query for posts WITHOUT providing a post ID (ordered by post date, and using the wp_ database prefix):

SELECT
    p1.*,
    wm2.meta_value
FROM 
    wp_posts p1
LEFT JOIN 
    wp_postmeta wm1
    ON (
        wm1.post_id = p1.id 
        AND wm1.meta_value IS NOT NULL
        AND wm1.meta_key = "_thumbnail_id"              
    )
LEFT JOIN 
    wp_postmeta wm2
    ON (
        wm1.meta_value = wm2.post_id
        AND wm2.meta_key = "_wp_attached_file"
        AND wm2.meta_value IS NOT NULL  
    )
WHERE
    p1.post_status="publish" 
    AND p1.post_type="post"
ORDER BY 
    p1.post_date DESC