New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL limit returning results way too slow.
Hey,
I have a MySQL database and I am trying to pull records out of it using limit.
The table has around 7 million records, and it takes few good seconds to fetch all the data I need.
Showing rows 0 - 19 (20 total, Query took 5.2566 sec)
SELECT * FROM `table` ORDER BY votes DESC LIMIT 1000000 , 20
explain returns:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE quotes ALL NULL NULL NULL NULL 2544640 Using filesort
However if I select only votes, it is much much faster, but selecting only votes does not help me. I need another 3 columns.
One of them is a TEXT column.
Showing rows 0 - 19 (20 total, Query took 0.3135 sec)
SELECT votes FROM `table` ORDER BY votes DESC LIMIT 1200000 , 20
explain returns:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE quotes index NULL votes 4 NULL 1000020 Using index
Do you guys have any ideas on how I can make this faster?
Comments
And what about
Showing rows 0 - 19 (20 total, Query took 5.4298 sec)
SELECT id, post, timestamp, votes
FROM
table
ORDER BY votes DESC
LIMIT 1100000 , 20
Same results.
The TEXT column is what's causing the slowness. Columns that have a variable length (TEXT, BLOG, VARCHAR, etc) will always cause queries to be slow, especially in a table that's got a few million records.
I see. any ideas on what can I do to have it return faster?
If I create a new table for the TEXT column with an ID for the post, and select using joins, will it help?
I don't think that will actually help, unfortunately. Do you really need to be using a TEXT column? Do you have an index defined for the table?
Yes I do need text, to store the post contents.
I have an index set up on the ID,timestamp,votes columns.
I am sorry, but, I guess you can't do anything but optimize your cache size in MySQL.
If you have an index, make your SELECT to be exactly as the index. The thing is, as you have
filesort
it indicates that your index is not used.How about using 2 queries?
@kylix
It wont help me selecting only the votes column.
As I also need the TEXT field, or at least the ID field, and the rest I will do with extra queries.
@vedran
The first query takes about 2-3 seconds to complete, and the second one takes around 6 seconds.
Which somehow comes out that it is faster to use the normal query
What about something like this:
How about this?
Just out of curiosity, how long does it take to do
Maybe you didn't understand what I implied: Make your Index and SELECT fit. If you need to select more columns make your index fit. Your EXPLAIN clearly shows that your index is not used and MySQL searches through the whole table. However, since you're also searching on the TEXT it would be good to make two tables. The first one with the normal columns where the searching will take place and the second table with the TEXT linked via ID.
@TigersWay
It takes
Showing rows 0 - 29 (2,544,640 total, Query took 2.4919 sec)
Which is a-bit better now.
@vedran
The first query takes:
Showing rows 0 - 19 (20 total, Query took 2.5673 sec)
The second one:
Showing rows 0 - 19 (20 total, Query took 0.0009 sec)
The random id takes:
Showing rows 0 - 0 (1 total, Query took 0.0004 sec)
@kylix
I tried that, but for some odd reason selecting the ID's itself takes few seconds.
That's strange, can someone explain why
takes only 0.3135 sec
and
needs 2.5673 sec
It's like the index in the last query is not used. Maybe creating an index on (votes, id) (or perhaps (id, votes)) could help.
Why are you using limit ? Your just throwing away results
Will something like
SELECT ID from table where id =>1000000 order by votes limit 20
work ?
Actually now they take the same time, I have set a correct index now.
SELECT id,votes FROM quotes ORDER BY votes DESC LIMIT 1340000 , 20
takes:
Showing rows 0 - 19 (20 total, Query took 0.3924 sec)
But now I need a solution to fetch the other data quickly.
This for example takes a-bit too much time in my opinion:
Showing rows 0 - 19 (20 total, Query took 1.0731 sec)
SELECT *
FROM quotes
WHERE id
IN ( 9578861, 9568921, 9558981, 9549041, 9539101, 9529161, 9519221, 9509281, 9499341, 9489401, 9479461, 9469521, 9459581, 9449641, 9439701, 9429761, 9419821, 9409881, 9399941, 9390001 )
LIMIT 0 , 30
@exussum
No it won't work since I can have missing id's due to them being deleted, I can have huge gaps in-between.
I can have id's going from 0 to 300k, and then have another 100k id's missing. so it wont be as accurate as I need it to be.
Maybe sorting id's would work a bit faster
the id,votes index could be used there. Im pretty sure the votes,id one wont be
What do you guys mean sorting the id's?
Dekken maybe showing us the table structure would help too