MySQL Forums
Forum List  »  PHP

Slow 'sending data' phase on mySQL query, for simple query
Posted by: Steve Childs
Date: March 20, 2010 01:03PM

I've got an INNODB table with around 8m records in it. I'm doing a query to fetch just a subset (around 500k) of those, but its taking over 12seconds to send the data!

This was the result on sending of 100k records...

SELECT user_id,ui.country_id,ui_id,ui.ind_id,ui.town_id,ui.ui_prod_rate, bus_id,ui_status FROM user_industries as ui WHERE user_group_id = '1' AND ui_status != 'disabled' ORDER BY ui.user_id LIMIT 0,100000

Query took 2.1204 sec

// Excerpt from Query Profile
Sending data 2.119942

// Query Explain:
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE          ui	ref	idxTest	      idxTest	4	const	958750	Using where

idxText is an index on user_group_id,user_id

Its a pretty simple query, no joins etc.. and I've setup the indexes to optimise the query as much as I can, I cannot understand what is causing the delay! The server is a Win 2008 server, running PHP 5.2.5 and mySQL 5.0.45

Is it a php thing or a mysql thing?

If I used the C library instead of php, is that likely to be the answer? As far as I can tell, I can't see it would be as its simply calling mysql_query anyway.

Thanks & any help would be gratefully received.

Edited 1 time(s). Last edit at 03/20/2010 01:14PM by Steve Childs.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.