fenway

STR_TO_DATE() is your friend.

Wouldn't a index on "d.tarrif " be better... from what I see he already has an index on "h.tarrif_numericid". Actually, not at all -- mysql joins "left deep", so it won't use the left-sided table columns for index lookups -- it already has "found" those rows.

The DATETIMEs you can't covert you don't need to convert -- you must be confused.

Glad to help.

You need an index on numericid. And don't use DISTINCT.

You want this.

As long as you're in the valid TIMESTAMP range, then yes -- it's not useful for things like birthdays, but then again, those aren't subject to timezones, and shouldn't have been DATETIMEs anyway.

Well, if separate games from teams, you won't need a union.

If you used TIMESTAMP, you'd have do absolutely nothing.

I just mean that if a simple query like this is crazy, then the design needs to be re-worked. I didn't have a specific recommendation, but I can think of a few if you're interested.

Well, yes, that design is far from ideal. You can just UNION the results.

Yes, mysql will cache indexes -- and there are ways to "warm" the cache.

That's all I can do without redesigning everything.

That's not how full-text works. If you want to search across columns, then you need to create a full-text index across those same columns. Those individual column full-text indexes aren't going to be used for the query provided -- there's no "index merge" for full-text.

Ah... then my GROUP BY in the beta_keys table should read "GROUP BY `users`.`id`" instead.

Then you don't have a FULLTEXT index in place. In fact, I don't even see a single key. Post the SHOW CREATE TABLE.

So what's the problem?

This is what I have so far... but I'm confused about ref_id vs user_id in the beta_keys table. SELECT CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`, CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`, `companies`.`companyvideo` AS `video`, `investor_info`.`accredited` AS `accredited`, ( SELECT COUNT(`user_id`) FROM `blog_posts` WHERE `user_id` = '1' ) AS `post_count`, ( SELECT COUNT(`uid`) FROM `votes` WHERE `uid` = '1' ) AS `vote_sent_count`, `votes_received`.`inv_vote_received_count`, `votes_received`.`pub_vote_received_count`, ( SELECT COUNT(`userid`) FROM `employees` WHERE `userid` = '1' ) AS `joined_count`, `votes2`.`ent_count`, `votes2`.`inv_count`, ( SELECT COUNT(`company_id`) FROM `company_fans` LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid` LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid` WHERE `userid` = '1' ) AS `company_fans_count`, ( SELECT COUNT(`user_id`) FROM `recommendations` WHERE `user_id` = '1' ) AS `recommendation_count`, ( SELECT COUNT(`partner_id`) FROM `partners` WHERE (`user_id` = '1' OR `friend_id` = '1') AND `approved` = '1' ) AS `associate_count` FROM `users` LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id` LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` LEFT JOIN ( SELECT `employees`.`userid`, SUM( IF( `votes`.`vote_type` = 1, 1, 0 ) ) AS `inv_vote_received_count`, SUM( IF( `votes`.`vote_type` = 0, 1, 0 ) ) AS `pub_vote_received_count` FROM `votes` INNER JOIN `employees` ON `employees`.`companyid` = `votes`.`company_id` WHERE `employees`.`userid` = '1' GROUP BY `employees`.`userid` ) AS `votes_received` ON `votes_received`.`userid` = `users`.`id` LEFT JOIN ( SELECT `beta_keys`.`ref_id`, SUM( IF( `users`.`accounttype` = 1, 1, 0 ) ) AS `ent_count`, SUM( IF( `users`.`accounttype` = 0, 1, 0 ) ) AS `inv_count` FROM `beta_keys` INNER JOIN `users` ON `users`.`id` = `beta_keys`.`userid` WHERE `beta_keys`.`ref_id` = '1' AND `users`.`status` = 3 GROUP BY `users`.`id` ) AS `votes2` ON `votes2`.`ref_id` = `users`.`id` WHERE `users`.`id` = '1' ;

I don't understand the question.

That doesn't make any sense. Vote UID has nothing to do with the USER uid.

Show us EXPLAIN otuput.

You should be using LEFT JOIN... IS NULL.

Then if would be vote.id, not vote.uid, right?

" ON `vote_sent`.`uid` = `users`.`id`" doesn't make any sense to me. Why is there no user id in the vote table?

I have no idea why you're doing any of this.