Databases
StackOverflow2013
Postsdbo
POindexes in mysql SELECT AS or using Views
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POindexes in mysql SELECT AS or using Views
    text
    copied!<p>I'm in over my head with a big mysql query (mysql 5.0), and i'm hoping somebody here can help. </p> <p>Earlier I asked how to get distinct values from a joined query <a href="https://stackoverflow.com/questions/508707/mysql-count-only-for-distinct-values-in-joined-query">mysql count only for distinct values in joined query</a></p> <p>The response I got worked (using a subquery with join as)</p> <pre> select * from media m inner join ( select uid from users_tbl limit 0,30) map on map.uid = m.uid inner join users_tbl u on u.uid = m.uid </pre> <p>unfortunately, my query has grown more unruly, and though I have it running, joining into a derived table is taking too long because there is no indexes available to the derived query. </p> <p>my query now looks like this</p> <pre> SELECT mdate.bid, mdate.fid, mdate.date, mdate.time, mdate.title, mdate.name, mdate.address, mdate.rank, mdate.city, mdate.state, mdate.lat, mdate.`long`, ext.link, ext.source, ext.pre, meta, mdate.img FROM ext RIGHT OUTER JOIN ( SELECT media.bid, media.date, media.time, media.title, users.name, users.img, users.rank, media.address, media.city, media.state, media.lat, media.`long`, GROUP_CONCAT(tags.tagname SEPARATOR ' | ') AS meta FROM media JOIN users ON media.bid = users.bid LEFT JOIN tags ON users.bid=tags.bid WHERE `long` BETWEEN -122.52224684058 AND -121.79760915942 AND lat BETWEEN 37.07500915942 AND 37.79964684058 AND date = '2009-02-23' GROUP BY media.bid, media.date ORDER BY media.date, users.rank DESC LIMIT 0, 30 ) mdate ON (mdate.bid = ext.bid AND mdate.date = ext.date) </pre> <p>phew!</p> <p>SO, as you can see, if I understand my problem correctly, i have two derivative tables without indexes (and i don't deny that I may have screwed up the Join statements somehow, but I kept messing with different types, is this ended up giving me the result I wanted). </p> <p>What's the best way to create a query similar to this which will allow me to take advantage of the indexes? Dare I say, I actually have one more table to add into the mix at a later date. </p> <p>Currently, my query is taking .8 seconds to complete, but I'm sure if I could take advantage of the indexes, this could be significantly faster. </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