0

I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table.

First: is this true , or not?

Second: is there any way to increase it's speed again? (sth like Defragment maybe!)

Improve this question
6
  • first, which database engine are you using ? MyISAM or InnoDB ?
    – Raptor
    Commented May 16, 2013 at 7:17
  • i'm using innoDB.
    – faridzs
    Commented May 16, 2013 at 7:18
  • 1
    Well if you delete rows, ofcourse it will be faster as less rows are being searched in.
    – eric.itzhak
    Commented May 16, 2013 at 7:21
  • spend a nickel for a good consultant
    – Drew Pierce
    Commented May 16, 2013 at 7:25
  • Are you using innodb_file_per_table ?
    – RolandoMySQLDBA
    Commented May 20, 2013 at 19:29

1 Answer 1

Reset to default
2

If you doing on a regular basis UPDATE and/or DELETE statements on a table the SELECT speed will decrease over time because the data and indexes (if present) will be fragmented thus the search process will take more depending on the degree of fragmentation so i purpose :

  • to run OPTIMIZE on the afflicted table on a regular basis (once a day should be enough) because this will defragment the data and rebuild the indexes (if present)
  • add indexes (if you do not already have) on the columns most commonly used in SELECT statement
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.