SQL, vast query improvements needed

[cthree]

Suggestion:

Optimize the SQL queries.

Start by removing conditional operators from WHERE clauses, especially OR. MySQL cannot optimize a SELECT containing OR. The optimizer is not able to pick an index to use when OR is used so you get a full table scan.

Next cut down the complexity of some queries to get simpler results. Do multiple queries instead of one big one. Complex queries take up massive amounts of server RAM for buffering the massive result. This means more per connection overhead and less RAM left for key buffers. You are going to get vastly improved performance by doing 4 simple queries rather than 1 query joining 4 tables, especially if the query selects a TEXT column (ie ibf_posts, ibf_members, etc.) Take what you need when you need it. It may sound like it's efficient to make one massive query then munch the results but the database doesn't work that way.

Try setting:

log-queries-not-using-indexes

in your server to see all of the queries which scan tables. Those can't be cached nor optimized, probably require temp tables and those are also probably written out to disk:

Created tmp disk tables 10405
Created tmp files 523
Created tmp tables 11369

That's with a max_heap_table_size and tmp_table size set to 32MB.

[cthree]

Here's a sample of what I mean in the first case (operators in WHERE) from Login.php:

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 45152
SELECT id, name, mgroup, password, ip_address FROM ibf_members WHERE LOWER(name)='fjw';

This is from my slow query log with log-queries-not-using-indexes turned on. Yes, it only took one second but it also had to scan the entire members table. I'm not sure if this query is relevant to 2.x but there are lots more like it. In this case it would be far more efficent to store the lower case username in the members table and do a straight select for it SELECT ... WHERE lcname='" . strtolower($username) . "'". It would have taken no time (assuming this lc name was indexed).

[Wolfie]

When you say "2.x", I would presume that you mean 2.0.x as well as 2.1.x

If that presumption is correct, I will tell you that there are fewer queries and the cache-system is designed to not only help reduce the queries but also to reduce the time spent doing queries.

If you are griping about v2.0.x, then I think that the fact that there haven't been a ton of complaints about excessive memory usage (from hosting companies of all people) would give indication that your conclusion from your analysis may be inaccurate. I'm fairly certain that Matt (and team) have taken the time to study up on the pros/cons of doing the queries in the manner they are being done, and have done everything in their programming power to ensure that how things are done is the maximize results while minimizing the use of resources.

Give Matt some credit, ok?

[cthree]

Database code is vastly improved in 2.x. I haven't seen the 2.1 code so I can't comment on it. This is an area that needs constant improvement. Search (FULLTEXT) hasn't changed and it still unusable (to slow, bottleneck, connections exhausted).

My situation is unique and I'm not surprised that there aren't many complaints. Hosting companies run lots of little forums not big ones. ALL of my searches timeout waiting for results, when run by hand they take between 30 seconds and 16 minutes. There is a big difference between searching 5.5 million rows in one table and searching 5500 rows in 1000 tables.

I'm not griping about anything. I'm offer a suggestion for improvement. I've fixed most of the queries to work reasonably well and even Matt himself spent about a week tweaking my queries to make them run at a reasonable speed. If IPB wasn't any good I wouldn't be using it.

[Wolfie]

but in your first post of this topic, you were giving instructions on what to do as though Matt and the others have no concept of how to do this stuff.

Present the problem, the solution you have tried and has worked for you. Let them decide what to do with it.

:)

[cojo]

Dacity, you're only a customer here not an employee. Other customers can word their suggestions whatever way they wish. If the IPS team has a problem understanding something or takes offense to the wording, they all are old enough to speak for themselves.

cthree submitted a product improvement suggestion and was not putting down or griping about the employees or product. Software which does not continually improve ends up dying.

[Wolfie]

Dacity, you're only a customer here not an employee. Other customers can word their suggestions whatever way they wish. If the IPS team has a problem understanding something or takes offense to the wording, they all are old enough to speak for themselves.

Your own point works against itself. I'll let you figure out what I mean.

[lancefiasco]

Compared to the competition, IPB uses far fewer queries, which is why we could be going back to IPB soon.