Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
One thing that jumps out straight away in the use of LIKE. It is usually faster to use = when possible. – JHFB May 29 '12 at 16:14
Yes I know but upload_tags field are tags separated by comma and the text can be in the middle. But my question is, is slow queries can cause other queries to be slow too. – jcubic May 29 '12 at 16:27
Rememeber that using a wildcard as teh first character precludes indexes from being used and this will get slower and slower as the number of records increases. If this is acomma delimited list, you definitely need to split it out to a related table. – HLGEM May 29 '12 at 17:39
4  
Also if it is a comma-separated list, WHERE upload_tags LIKE '%foo%' will match 'bar,gumfoota,blat' whereas what you probably want is WHERE ',' + upload_tags + ',' LIKE '%,foo,%'; - this still won't be any faster but at least it will be correct. The right answer is to store these values separately, as @HLGEM suggested, since they are, in fact, separate pieces of data. I can't understand why so many people want to stuff all kinds of quasi-related data into a single column. – Aaron Bertrand May 29 '12 at 19:25
jcubic, I edited your question for grammar and clarity. Does it still convey what you meant? – Nick Chammas May 29 '12 at 19:26
show 3 more comments

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
Great that's what I wanted to know. – jcubic May 29 '12 at 16:40
You should think about splitting that string with all those tags... – Borik May 29 '12 at 16:55
Yes, I know I need to create separatad table for tags (along with other optimizations). – jcubic May 29 '12 at 16:57
up vote 1 down vote

Yes, you need to rethink your schema and your queries.

foo LIKE '%...' -- cannot use an index on foo

9 seconds to INSERT one row -- other queries were hogging the table. (There are many possible explanations for how/why.)

Consider FULLTEXT instead of LIKE

Consider having a separate table with "tags". It will be many-to-one with the main table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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