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.