0

I have a table contains 5000 records . When i am querying the table (with 30 columns) for a selection of a 500 records itself , its taking 30 seconds . How can i reduce this time of retrieving data from MySQL ? I was indexing 3 main integer type columns in the table including primary key . Is there any other workarounds ?

Sample query : select * from my_table where my_condition LIMIT 500

KTM
  • 858
  • 4
  • 21
  • 43
  • You need to specify "my_condition" and also the table definitions. For a small table of only 5000 rows it should not take a lot of time to run a query. I would guess that you need a new index but without knowing what you search for in your condition and how the tables look it is hard to tell. We need more info.. – MrApnea May 22 '17 at 14:46
  • The conditions is nothing but a simple : **where id !="this" AND another_id ="this"** , I will post all the columns and its types soon – KTM May 22 '17 at 14:52
  • If that is the query that takes a lot of time, try using a combined index on both columns and see if that makes any change in time. Some more info here about index with multiple columns: https://stackoverflow.com/questions/12728832/understanding-multiple-column-indexes-in-mysql-query – MrApnea May 22 '17 at 15:00
  • A table with 30 columns *can* be symptomatic of poor design. Incidentally, LIMIT without ORDER BY is fairly meaningless. – Strawberry May 22 '17 at 15:03
  • 2
    You might like my presentation [How to Design Indexes, Really](https://www.slideshare.net/billkarwin/how-to-design-indexes-really). Search for it on YouTube to find a video of me presenting it. – Bill Karwin May 22 '17 at 15:04
  • That's my Friday night sorted. – Strawberry May 22 '17 at 15:05
  • Do you have `INDEX(another_id)`? Please provide `SHOW CREATE TABLE` _and_ the actual `WHERE` clause. The `WHERE` clause makes a _huge_ difference to optimization! – Rick James May 22 '17 at 17:32

1 Answers1

1

For a query of a table with 5000 record to take 30 seconds you must by starving you DB of memory or having very slow disk. While indexing will help, it will only help if you don't have slow disk and no memory.

iceraj
  • 359
  • 1
  • 5