1.5 Million Row Table, Queries take forever.

[kjtocool]

I have a 1.5 million row table, it happens to be a posts table for a large forum.  Each query that hits this table takes a long time (3 to 10 seconds).  The table has various keys:

 

Keyname Type Cardinality Action Field 
PRIMARY  PRIMARY 1430198       post_id  
forum_id  INDEX None       forum_id  
topic_id  INDEX None       topic_id  
poster_ip  INDEX None       poster_ip  
poster_id  INDEX None       poster_id  
post_approved  INDEX None       post_approved  
tid_post_time  INDEX None       topic_id  
post_time  
post_subject  FULLTEXT None       post_subject  
post_text  FULLTEXT None       post_text  
post_content  FULLTEXT None       post_subject  
post_text  

 

Doin an explain on a few of the slow queries showed me that possible_keys, key, key_len and ref are all NULL. I am guessing, but am fairly certain that the lack of a key on a 1.5 million row table is what's causing the issue. I can't imagine searching all 1.5 million posts each time is a very efficient way to go about things.  One of the slow queries:

 

SELECT post_id, topic_id, forum_id 
FROM phpbb_posts 
WHERE topic_id = 51544

 

Returns three rows, takes an average of about 3 and a half seconds.  Explain on the query above returns:

 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE phpbb_posts ALL NULL NULL NULL NULL 1430559 Using where 

 

The things that jump out to me are that A) the rows searched about 1.5 million and B) there are no indexes used, so it has to search ALL 1.5 million rows.

 

As this forum continues to grow, my understanding is it's going to get slower and slower. Unless I'm mistaken, the fact it must search ALL post records is the reason for the slow response. I am hoping that A) someone can confirm this and B) someone can tell me if there is anything I can do to speed up queries hitting the phpbb_posts table.

[kickstart]

Hi

 

Not sure but the best I can make out is that the index is being ignored because of the cardinality of none. Possibly doing an optimise on the table might force this to be set correctly.

 

All the best

 

Keith

[roopurt18]

Make sure your table stats are up to date; I'm not sure what the exact command in MySQL would be but in general the database system keeps analytical stats on its tables.  It then uses these stats to determine if the use of an index may be appropriate.  If the stats become too far out of sync with the index, then the indexes are not used.

 

You may also have too many indexes and may be better off combining them.

 

MySQL will also skip using the table if all of the data it needs lies within the index.  For example, in your query:

SELECT post_id, topic_id, forum_id 
FROM phpbb_posts 
WHERE topic_id = 51544

Sure, MySQL can use your topic_id index, but it's still going to have to pull post_id and forum_id from the actual table.  For that particular query, an index like this may be more appropriate:

create index idx_topic_forum_post on `thetable` (topic_id, forum_id, post_id )

Since the three requested columns exist entirely in the index, MySQL can skip looking at the actual table altogether.

 

Also keep in mind that the more indexes you have, the slower inserts and updates become.

[kjtocool]

I tried an optimize, didn't affect the cardinality, nor did it change the results of the explain.  From what I know of cardinalities, there sure shouldn't be NONE for every index other than the primary key, and I'm sure that's one of the reasons it's forced to search all rows.  I tried optimizing, repairing and analyzing the table.  Everything came back looking normal, but no change was seen in the cardinalities.

 

I'm interested in the idea of table stats, but through a quick google search i wasn't able to find anything I understood that could help.  If anyone knows more about table stats or analytical stats and any relevant command that would let me shed some light onto what is going on here I would really appreciate it.

[kickstart]

Hi

 

For comparison, the index details for the posts table on a phpBB board I have (rather smaller).

 

Keyname Type Cardinality Action Field

PRIMARY PRIMARY 25369  post_id

forum_id INDEX 234  forum_id

topic_id INDEX 5073  topic_id

poster_id INDEX 2818  poster_id

post_time INDEX 25369  post_time

 

All the best

 

Keith

[kjtocool]

Yep, that's my problem then.  The cardinalities are all wrong.  My selects aren't able to discount rows based on forum_id (which would limit searchs from 1.5 million rows to no more than 420,000 on the largest forum).  If I can figure out why the cardinalities of those additional indexes aren't correct, or how to rebuild them, I'll solve the issue.

 

Any ideas?  ???

 

An analyze SHOULD fix this issue, but it's not in my case.

[kjtocool]

I think I found my issue!

 

I did the following query: SHOW INDEXES FROM phpbb_posts, which showed me that my keys were all disabled.  I then ran the following query: ALTER TABLE phpbb_posts ENABLE KEYS

 

That query is currently running, and my guess is it will take quite a while as it will have to rebuild all the indexes.  But, assuming it works, that SHOULD solve my issue.  Wish there was more visibility into the keys being turned off!

[roopurt18]

MySQL will also skip using the table if all of the data it needs lies within the index.  For example, in your query:

Code: [select]

SELECT post_id, topic_id, forum_id

FROM phpbb_posts

WHERE topic_id = 51544

Sure, MySQL can use your topic_id index, but it's still going to have to pull post_id and forum_id from the actual table.  For that particular query, an index like this may be more appropriate:

Code: [select]

create index idx_topic_forum_post on `thetable` (topic_id, forum_id, post_id )

Since the three requested columns exist entirely in the index, MySQL can skip looking at the actual table altogether.

 

Just curious, did you consider any of that?

[kjtocool]

Just curious, did you consider any of that?

 

At this point, no.  And only because I know there is an issue with my current indexes.  I figure it's more important to get the current indexes working before I start trying to optimize them even more.

 

As is, since all the keys are disabled, even had I added that index, it would run into the same issue where it would have a cardinality of none and never be used.  Assuming enabling keys does fix my indexes/cardinality issue, I may then consider adding the index you suggested to test out the possible gain.

[fenway]

The keys were disabled? I've never heard of that happening "on its own"...

[roopurt18]

I actually didn't even know keys could be disabled.  Learn something new all the time!

[fenway]

Yeah, I do it often for bulk inserts.