fenway

Sure... if you have an integers table.

Really? There's a DATE() function in mysql so that you do'nt need any of this magic.

What does this have to do with mysql?

There's a sticky on this topic.

No only is it not a good idea, it's a terrible one.... leave the formatting to the WEB.

Hmm... nothing wrong there...

It doesn't matter where the parameter comes from... you need to pass it and sent to the SP.

Run the equivalent select and show us the EXPLAIN.

Not a problem -- but make you that you remove ALL the other indexes -- including the one just on ( user_id ) as well any other covering ones we made in the process -- otherwise you'll have extra overhead for no reason. I'd like to confirm what the final index that actually worked was... hard to tell from the expalin.

paren = ( ) bracket = [ ] brace = { }

As in: select memberType, COUNT(*) as cnt from members WHERE verified=1 GROUP BY memberType

Yes, but even if it doesn't, the LIKE clauses in the column list are evaluated on a limited recordset (i.e. the matching rows), so it's neglible.

That's nice... how about sharing your solution?

The php refman is your friend.

TLDR... can you simplify your issue?

You don't select last_insert_id "from" anywhere. See here for a better way.

Hmmm. still not ideal. Can you try a few things? 1) switch to ASC -- just for now. 2) try switching the order of the keys in the index, then try ASC/DESC.

It is -- in the WHERE clause -- but you're already paid that performance penalty. And as mentioned earlier, drop UPPER() unless you've explicitly requested a case-sensitive collation.

Well, you're not checking the return value of the mysql_query() call, nor capturing mysql_error() -- nor echoing the actual insert statement itself.

Well, you're never pulling back the data... how do you know it's not being added?

I'm hoping the optimizing handles this... even if it does, evaluating LIKE in the column list isn't expensive.

Seriously? You're opening the block in the wrong place... write a proper if/else loop. edit: sending a PM to a moderator about basic PHP syntax in a mysql forum? bad idea.

ALTER yourTable ADD INDEX user_history_datetime_user_id ( user_history_datetime, user_id ); Make sure this index is actually being used in the EXPLAIN -- otherwise, you'll have to force it.

Then get LAST_INSERT_ID() from mysql -- php has a function for this, I think -- and pull back the newly inserted record.

Well, of course it's slower... that's why mysql made the "right" choice. Much better to find 30K (0.5%) records and sort them, then to sort all 5M!!! I'd say that you could make a composite index on ( user_id, user_history_datetime ), but I'm not sure that it would work since you want DESC for the 2nd half of the key. In fact, try it 'backwards' -- composite index on ( user_history_datetime, user_id ).