Q: I run a site in my sig. It contains a large database (about 800k rows) in about 50 tables. There are at least 40 different SELECT queries used to access data. Now I talk to someone who claims that the indexes would not help because basically everything should be indexed. Now, if indexes will not help, what is the solution for this? After a dedicated Xeon server for a site with only a few thousand visitors per day is not reasonable.
I am convinced that this is a common scaling problem, Im just not sure what the normal solution.
Virtual Private Server (vps) with Web Interface, for MetaTrader Forex
Re:SELECT DISTINCT is almost always an orange flag, anyway, if not red. See this page (http://www.databasejournal.com/features/postgresql/article.php/3437821) for some alternative strategies.
But I don't know how often that case is executed. Using the regular expression matches in the WHERE clauses of both cases shown wouldn't be helping either.
Based on what I can see here I would definitely have indexes on author.lastname and frbr_work.title, but this is just a guess based on this small slice of your code.
Pingback Optimizer
Re:Let me try to give a few more details. For every insert/alter, I would say there are 2-3000 selects. Also, queries tend to be across multiple tables. There are about 50 tables but most of the data is pulled from about 10 of them. I am not sure that much can be done with the queries themselves. The only thought I have had was caching, but even that is not a solution.
I am not sure what a query plan is.
Below are a few of the queries run on one of the pages. This particular page lists all the records in the database based on what the user is looking for.
Google Top Ten Optimization
Re:Do you do a lot of inserting or is it mostly querying against existing data? If there's not a lot of change to the data I would think that the up front cost of the indexing would be well worth it.
Setting Up A Web Server.
Re:It's really very hard to say without knowing every single detail. There are cases where index may help and there are cases where they won't. It all depends how the data is distributed and what kinds of select statements you run.
The Original TweakBoy’s PC Optimizer
Re:This is a big question, and honestly it sounds like the person you're taking it over from doesn't necessarily know what they're doing. At least, he's not an expert on databases.
And I'm not either, at least not MySQL. Can you run a query plan with that db? If so then you can see exactly what strategy the database engine is using to complete the queries. If everything devolves into a table scan then that explains a lot.
Another source of database performance issues is high degrees of normalization requiring a lot of complex views to knit things back together for each transaction.
If you can post more information about the schema maybe we can see something there.
0 Comments.