Portal Home > Knowledgebase > Articles Database > Please help me with a MySQL query..
Please help me with a MySQL query..
| Posted by akbsol, 02-27-2008, 10:06 AM |
| I have MySQL 4.0.x and a 2 GB table called "people" with approx 1200000 rows in it having columns:
id (PRIMARY AUTO INCREMENT)
name
email
group_id VARCHAR(8) (INDEXED)
address
data
reg_time (INDEXED)
ip
status
phone
There are approximately 14000 different group ids. I am facing a problem with a certain group which has approximately 25000 members. To search 300 members of that group starting from offset 15000 i do this query:
This takes approximately 35 seconds before MySQL returns the records even though the column group_id has a INDEX on it.
To check further I created a separate table "people_idx" with just two columns "id" & "group_id" from the main table. Now I use following PHP to fetch names of 300 people starting offset 15000 from that group:
Even though this performs 300+1 different queries it returns back in less than a second. Now I tried this join:
This also takes more than 30 seconds. Now I am confused about what I am doing wrong and which approach should I use. Please anyone help me out.
Thanks in advance.
Regards,
Akash
|
| Posted by akbsol, 02-27-2008, 11:50 AM |
| Here are few EXPLAIN outputs:
EXPLAIN SELECT people.name FROM people, people_idx WHERE people_idx.group_id = 'b67k219f' AND people.id = people_idx.id LIMIT 15000, 300
DESC people;
DESC people_idx;
SHOW KEYS FROM people;
SHOW KEYS FROM people_idx;
|
| Posted by Sillysoft, 02-27-2008, 04:20 PM |
| For testing purposes change your original query from:
SELECT `name` FROM `people` WHERE `group_id` = 'b67k219f' LIMIT 15000, 300
to:
SELECT id FROM `people` WHERE `group_id` = 'b67k219f' LIMIT 15000, 300
And see if that has the same loading problem.
|
| Posted by akbsol, 02-27-2008, 10:30 PM |
| Yes same problem.
|
| Posted by foobic, 02-27-2008, 11:08 PM |
| I wonder if it's slow counting over the 15000 variable-length records. Not sure why it wouldn't use the index there though. If you change to "LIMIT 150, 300" does that make a big difference?
As a closer approximation to your php script you could also try a subquery:
Edit: Just noticed you're on version 4.0 - no subqueries for you! Isn't it about time you upgraded?
|
| Posted by akbsol, 02-28-2008, 10:58 PM |
| I have also learnt that fixed length char is considerably faster than a varchar. But the table is dynamic in nature as it has few other varchars. Even if the change group_id from varchar(8) to char(8), MySQL automatically converts it back to varchar.
Regarding the sub-query, actually I do not wish to use any sub-queries, JOINs and wanna perform a straightforward query of type:
SELECT * FROM people WHERE group_id = 'abcdef' LIMIT x, y
I am using a separate table people_idx solely because the above query performs very slow when offset is over 15000. On offsets below 2000-3000 it runs well which makes me assume that its not at all using indexes. Now I wonder why its not using indexes for group_id in main table "people" but uses it in a separate table "people_idx".
I should have upgraded to 4.1 (not 5.x due to resource constraints) but I am still a illiterate as far as the knowledge of character sets in concerned. In 4.0 its system dependent while in higher versions you get to mingle with them which I wanted to avoid.
|
| Posted by seriesfan, 03-02-2008, 09:00 AM |
| you have a table with large data, result takes longer because it has to scan through 15000 rows and throw away,
try to change:
SELECT `name` FROM `people` WHERE `group_id` = 'b67k219f' LIMIT 15000, 300
to:
SELECT name FROM people WHERE group_id = 'b67k219f' AND (id BETWEEN 15000 AND 1799)
|
| Posted by seriesfan, 03-02-2008, 09:04 AM |
| should be (id between 15000 AND 15299)
sorry my typo.
|
| Posted by akbsol, 03-02-2008, 09:15 AM |
| I have finally got it resolved through a helpful suggestion in another forum. I created a covering index on all those columns which are fetched frequently and those which are searched for in WHERE clause. This way MySQL gives the result from index itself without touching the large table. The queries:
ELECT `name` FROM `people` WHERE `group_id` = 'b67k219f' LIMIT 15000, 300
SELECT name FROM people WHERE group_id = 'b67k219f' AND (id BETWEEN 15000 AND 15299)
are not similar. The first one is almost likely to give 300 rows while the second one may even give 0 rows if there are no entries for group_id = 'b67k219f' with ids between 15000 AND 15299 (very much possible in a 1000000 row table).
Anyways thanks for your help.
|
| Posted by seriesfan, 03-02-2008, 09:42 AM |
| SELECT `name` FROM `people` WHERE `group_id` = 'b67k219f' LIMIT 15000, 300
SELECT name FROM people WHERE group_id = 'b67k219f' AND (id BETWEEN 15000 AND 15299)
The first & second both lso give you 0 result if there is no entry for group_id = 'b67k219f'.
The second one searches group_id='b67k219f' within the range of id (15000 to 15299). If it found group_id='b67k219f' in the range still give you result. (very much possible in a 1000000 row table < you can use built in function "count" to give you 300rows).
|
Add to Favourites 
Print this Article
Also Read
