Mysql – Can a poor database layout cause performance issues for simple queries

MySQLperformancequery-performance

I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too?

For example, an insert into table that has about 100 000 rows takes 9 seconds to execute. Could it be caused by a bad database layout, or should I search for performance bottlenecks elsewhere?

I have 3 tables:

  • aiki_users
  • ocal_files (connected with both id and username not foreign keys/refeerences) I have tags in field (sparated by coma instead of separateded table)
  • ocal_favs with favorites (suppose to connected with ocal_files and aiki_users) but no real connection (foreign keys)

There are slow queries that use LIKE in this way: upload_tags not like '%pd_issue%'

or

select *
from ocal_files
where upload_tags LIKE '%summer2010%'

These take 5-7 seconds to run (on heavy load). On heavy load (right now) we have 200 active users (shown by Google analytics).

Some of the most frequent queries that use a lot of LIKE and RLIKE searches were replaced by Sphinx; those pages run fast.

Best Answer

Yes, slow queries will cause other queries to be slow.

Server has only finite amount of IO it can do, if it busy doing heavy LIKE operation, like in your case, it can NOT use index it will make other queries slow.

Related Question