Hi,
I'm using mysql with php and have recently made some effort optimizing all queries on a project and I noticed something strange and wanted to check if this is correct.
Lets say I have a table looking like this: CREATE TABLE `profile_visitors` ( `profile_owner` int(10) unsigned NOT NULL default '0', `profile_visitor` int(10) unsigned NOT NULL default '0', `timestamp` int(10) unsigned NOT NULL default '0', KEY `profile_owner` (`profile_owner`) ) TYPE=MyISAM;
Is there or rather, should it be any speed difference between the 2 queries below:
SELECT * FROM profile_visitors WHERE profile_owner="3432";
and
SELECT * FROM profile_visitors WHERE profile_owner=3432;
Would be nice to know the expected behaviour.. :) Currently running 3.23.56
Best regards, Eric
SELECT * FROM profile_visitors WHERE profile_owner="3432";
I think it depends on which version of MySQL you're using. Since "3432" is a string, I suspect MySQL 3.x may have to convert the column profile_owner values into a string so it can't use the index and it is forced to read all the rows in the table (pretty slow). MySQL 4.x will convert the "3432" into an integer and use that to find the row. In fact, if you have non-numeric values at the end of the number, MySQL 4.x will ignore the erroneous characters. So "3423abc" is really 3423. (It stops when it finds the first bad character.)
You can easily verify this by doing: Explain SELECT * FROM profile_visitors WHERE profile_owner="3432";
to see if it is using the index.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
