MySQL Table With Millions of Entrees

Hi, I'm working on a website and we have a MySQL table that currently has over a few million entrees that are records of individual clicks & purchases. It's becoming an issue because it it growing larger and larger and we are trying to add reporting functionality on the back-end which searches & counts these clicks, but the queries can take quite a while. Does anybody have any suggestions for optimizing this?

The simple answer is probably along the lines of adding date-based indexes to the table. A query can go from taking 100 seconds with a table having 1.5 million rows on a system with less than a GB of RAM and a 1Ghz processor to taking less than 1/10th of a second if the engine can use indexes to quickly eliminate rows.

I say date-based because date is usually something that is segregated in reports to begin with so they're naturally good columns for the engine to use for quickly weeding out irrelevant rows by the thousands. What you actually use would depend on the table structure and queries reports run.

http://dev.mysql.com/doc/refman/5.0/en/ ... dexes.html

There's always the chance you'll have to refactor something about your table in light of the reports you want to run. Sometimes table structures just don't work as-is for what you want from them.

Hopefully it doesn't look like I'm "talking down to you" or vice versa. I haven't seen you in awhile for starters, and I didn't see anything such as having added all of the indexes you can think of, references to handler_read_rnd_next, or a slow query log, so indexes seemed like a good place to start.

Yeah I haven't been replying as much lately

Sorry for the vague question, I don't really know too much about advanced database optimization, it helped though. I'll make an exception when you talk down to me, you're a genius.

Thanks Joe.

joebert is exactly right. Since the DB structures never work as-is for reports (way more important when dealing with 10 to the 6th records) and have to be refactor. Initial time creating the DB to churn out those details to optimize the DB to begin with it a major time-saving procedure with many other advantages. Slow queries waster more than just time. I'm terrified at flow charting a new DB design cause there so much pressure for me to hit the mark that I put on myself but luckily my DBs don't get a million records. So, while there's tons of optimizations there's initial configs that should just be a given. It really helps to know the reporting system that you will need but that's rare to know exactly but usually it's date oriented so INDEXing a date is always a good idea to just have automatically. Primary keys are of course auto indexed but usually aren't much help. Good programs can always use primary keys if you have freaks for programmers that can utilize primary keys but highly unlikely. So, learn about INDEXing columns and also make sure the structure of the db is close/representative to the data type being stored. For example, a first name field set to varchar(55) but all the first names are less than 12 letters is a problem. Use ENUM if possible for information that can utilize that fieldtype.

I'm not a db or programmer freak but I like your post and I haven't seen you around PH. I was kinda surprised at the post cause I think you've provided good insight on some other topics we were in. So, I assume like me it's just not your expertise and unfortunately I'm just not a freak really at one thing but do have value I think in some areas. I'll let you know when I identify those . Info systems do need a team. I call my team either elves or dwarves cause those types usually work in good unison with the highest level absolute advantage (ie the person whose best at a task actually doing it). I always assumed the keebler that was putting the chocolate in the middle was best at it and why the old guy just never did anything.

Anyway, for DB optimization these are good quick,easy configs that can reduce query time.

I think cardinality can be utilized in some regards either as a tweak or FYI cause I know phpmyadmin reports it on tables. Maybe a db freak will chime in with more optimizations that aren't just the standard in order to fill out the topic of million plus record DB optimization. joebert gave a mention to a couple of leads that i'm googling. And I don't mind the talking-down to but I didn't notice any on joeberts part either. I've never had anyone on this forum come off as rude to me and IMO has the best base of fast, honest help. I did see DM on a recent post kinda get into it but I think the author and DM stayed on topic and both had valid points. Of course, name-calling is assumingly not allowed on OZZU but also name-calling is awful cause it stops working towards a solution. Though, talking-down to sometimes makes the instructor unknowingly put it in lamest terms so it's even easier to understand so even people seeking help need to be prepared for text with those tones and connotations and be sure they don't misinterpret the help. Sometimes to explain it to someone who is asking for help, the condescending nature is a given. Usually reference manuals decription and usage is vague and doesn't "talk-down-to" and makes learning from very hard.