Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL limit returning results way too slow.
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.

MySQL limit returning results way too slow.

DekkenDekken Member
edited September 2011 in Help

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

    SELECT votes,col1,col2,col3 FROM table ORDER BY votes DESC LIMIT 1200000 , 20
    

  • 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.

  • kylixkylix Member
    edited September 2011

    If you have an index, make your SELECT to be exactly as the index. The thing is, as you have filesortit indicates that your index is not used.

  • How about using 2 queries?

    SELECT id, votes FROM table ORDER BY votes DESC, id DESC LIMIT 1000000 , 1
    
    SELECT * FROM table  WHERE votes < = $votes AND (votes < $votes OR id < = $id)  ORDER BY votes DESC, id DESC LIMIT 20
    
  • @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:

    SELECT * FROM table
    JOIN (SELECT votes FROM table ORDER BY votes DESC LIMIT 1000000 , 20) AS T2
    ON table.votes=T2.votes
  • How about this?

    SELECT id FROM table ORDER BY votes DESC LIMIT 1000000 , 20
    SELECT * FROM table  WHERE id IN ($id)

    Just out of curiosity, how long does it take to do

    SELECT * FROM table  WHERE id = {insert random id here}
  • kylixkylix Member
    edited September 2011

    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.

    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

    SELECT votes FROM table ORDER BY votes DESC LIMIT 1200000 , 20

    takes only 0.3135 sec
    and

    SELECT id FROM table ORDER BY votes DESC LIMIT 1000000 , 20

    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?

  • eva2000eva2000 Veteran
    edited September 2011

    Dekken maybe showing us the table structure would help too

    mysql> use dbname;
    mysql> show create table tablename\G
    mysql> show indexes from tablename\G
    
Sign In or Register to comment.