neoform Posted October 22, 2007 Share Posted October 22, 2007 Would it be considered normal for a query with 5 INNER JOINS on primary keys to take 2-3 seconds to run if there's about 180,000 rows? It seems as though I've done queries on bigger tables that took less time. I've already analyzed/optimized them, yet the speed remains the same. +----+-------------+----------+--------+---------------------------------+---------+---------+-----------------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------------+---------+---------+-----------------------------+--------+--------------------------+ | 1 | SIMPLE | sessions | range | PRIMARY,user_id | user_id | 8 | NULL | 178893 | Using where; Using index | | 1 | SIMPLE | profiles | eq_ref | PRIMARY,status1,status2,created | PRIMARY | 4 | const,core.sessions.user_id | 1 | Using where | | 1 | SIMPLE | info | eq_ref | PRIMARY,position,info | PRIMARY | 3 | core.sessions.user_id | 1 | Using where | | 1 | SIMPLE | users | eq_ref | PRIMARY,userid | PRIMARY | 3 | core.info.user_id | 1 | Using where | | 1 | SIMPLE | choices | eq_ref | PRIMARY,user | PRIMARY | 5 | const,core.sessions.user_id | 1 | Using where; Using index | +----+-------------+----------+--------+---------------------------------+---------+---------+-----------------------------+--------+--------------------------+ 5 rows in set Quote Link to comment Share on other sites More sharing options...
fenway Posted October 22, 2007 Share Posted October 22, 2007 Well, you're not using any index on the first table.. I can only assume you don't actually need 180K records? What's the query? Quote Link to comment Share on other sites More sharing options...
neoform Posted October 22, 2007 Author Share Posted October 22, 2007 Here's the query. SELECT sessions.user_id, profiles.status, profiles.category_id, users.username, info.city, info.state, info.seeking, info.gender, last_on FROM system_users_sessions sessions STRAIGHT_JOIN phpneoform_profiles_users profiles ON profiles.user_id = sessions.user_id STRAIGHT_JOIN dating_user_info info ON sessions.user_id = info.user_id STRAIGHT_JOIN system_users users ON sessions.user_id = users.id WHERE info.latitude BETWEEN 40.457782352941 AND 41.046017647059 AND info.longitude BETWEEN -74.3954 AND -73.5954 AND sessions.last_on > '2007-10-15 18:34:55' AND profiles.category_id = '1' AND profiles.status = 'approved' AND users.status = 'active' AND info.birthday BETWEEN '1967-10-23' AND '1987-10-22' AND info.gender = 'female' AND info.seeking = 'male' ORDER BY sessions.last_on DESC LIMIT 500 system_users_sessions: +--------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+-------+ | user_id | mediumint( unsigned | NO | PRI | 0 | | | session | char(20) | NO | MUL | | | | identity | char(20) | NO | | | | | posted_on | datetime | NO | MUL | | | | expires_on | datetime | NO | MUL | | | | last_on | datetime | NO | MUL | | | | temp_session | enum('no','yes') | NO | | no | | +--------------+-----------------------+------+-----+---------+-------+ phpneoform_profiles_users: +-------------+----------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------+------+-----+---------+-------+ | category_id | tinyint(3) unsigned | NO | PRI | | | | user_id | mediumint( unsigned | NO | PRI | | | | status | enum('pending','approved') | NO | MUL | | | | created_on | datetime | NO | | | | +-------------+----------------------------+------+-----+---------+-------+ dating_user_info +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | user_id | mediumint( | NO | PRI | | | | birthday | date | NO | MUL | | | | gender | set('male','female') | NO | | | | | seeking | set('male','female') | NO | | | | | zip | char(6) | NO | | | | | latitude | double | NO | MUL | | | | longitude | double | NO | | | | | city | varchar(255) | NO | | | | | state | varchar(255) | NO | | | | +-----------+----------------------+------+-----+---------+-------+ system_users +---------------------+------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+ | id | mediumint( unsigned | NO | PRI | NULL | auto_increment | | username | varchar(32) | NO | UNI | | | | email | varchar(100) | NO | UNI | | | | password | varchar(255) | NO | | | | | hash_type | varchar(64) | NO | | | | | salt | varchar(255) | NO | | | | | status | enum('banned','deactivated','active','notice','pending','user_deactivated','user_deleted','deleted') | NO | | active | | | created_on | datetime | NO | MUL | | | | email_expires_on | datetime | NO | | | | | password_expires_on | datetime | NO | | | | +---------------------+------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+ Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 I'm confused about "using index" on the sessions table... it doesn't seem to be using any index (e.g. for last_on). Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 the user_id key is actually a composite of (last_on, user_id). Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 the user_id key is actually a composite of (last_on, user_id). I see... nonetheless, I don't understand why it's "using index"... Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 It's a mystery to me as well as to why it takes so long to load a query that from what I can tell is supposed to be fully indexed.. maybe this is a bug? MySQL v5.0.45-community-nt-log Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 Well, you have straight join hints there... switch as INNER JOIN. Then remove parts of the where clause / order by clause until you get the indexing you want, then add the mback. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 When i take the straight joins out the optimizer moves sessions down, which causes the last_on to no longer be included as the cardinal index, which means I can't sort by last on without a filesort. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 I still don't understand why it won't use an index on the sessions table... you can always force it to use a particular index, but it should know... Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 It actually is using the userid index which *should* be the right one.. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 It actually is using the userid index which *should* be the right one.. Sorry, my bad -- I saw the NULL and simply assumed that meant that was the key. So why so many rows? Oh, you have a limit, so the rows is meaningless. Drop all where clauses except for sessions-based fields, and see what explain thinks. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 info.latitude BETWEEN 40.457782352941 AND 41.046017647059 AND info.longitude BETWEEN -74.3954 AND -73.5954 I pulled this out and it dropped down to 0.3seconds. Pretty annoying since that's a critical field, I need local people only. They're also mere approximations by using guestimated ranges, before I actually had the distance calculator that gave exact distances between both targets, but that was ridiculously slow when the table had more than 1000 rows.. grumble. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 Glad it's performing better... the way to handle this is to run the first 0.3 second query without it, then get back info UIDs, and then run a second query on the info table -- this can be a subquery, of course, or a derived table (probably better). BTW, ranges are very difficult to optimize. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 23, 2007 Author Share Posted October 23, 2007 a derived query will actually make it faster? I was avoiding subqueries like the plague because they never use the indexes... Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 I tried breaking it into subselects.. no matter what way i do the subselect, it's slower.. by about 3x Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 Subquery != dervied table -- at least if it's not correlated. Subqueries can be bad because they run for each row -- long as it's just run once per query, it's not as bad. Yes, there is no indexability; if you're really concerned about this, you can create a temporary table in memory, index it, and then join it properly. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 Nah.. Looks like I'm stuck with a 3 second query. I can't remove the location limitations and a subquery slows it down even more. bleh. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 Nah.. Looks like I'm stuck with a 3 second query. I can't remove the location limitations and a subquery slows it down even more. bleh. Why? Run the fast query, then limit the results in PHP. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 The fast query returns all users without localization.. with 180,000+ rows that'll end up being well over 5,000 depending on the search users do.. sorting that in php will certainly be slower. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 Hmmm... you can try adding the range limits to the ON clause in the JOIN with the info table.... Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 err....... explain.. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 err....... explain.. dating_user_info info ON sessions.user_id = info.user_id Add to the ON clause. Quote Link to comment Share on other sites More sharing options...
neoform Posted October 24, 2007 Author Share Posted October 24, 2007 Maybe I'm missing something, but you want me to add a WHERE range to the JOIN? STRAIGHT_JOIN dating_user_info info USE INDEX (position) ON sessions.user_id = info.user_id WHERE info.latitude BETWEEN ".(floatval($latitude) - ($_distance / 68))." AND ".(floatval($latitude) + ($_distance / 68))." info.longitude BETWEEN ".(floatval($longitude) - ($_distance / 50))." AND ".(floatval($longitude) + ($_distance / 50))." STRAIGHT_JOIN system_users users ON sessions.user_id = users.id ?? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 No, I want you to add those as ON conditions. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.