I have table like this
name | personal_number ----------------------------------------- Jon | 222 Alex | 555 Jon | 222 Jimmy | 999
I need get every name, which personal_number repeates in table more than 1, that is result must be:
So, Variant 1):
SELECT name FROM mytable WHERE personal_number IN ( SELECT personal_number FROM mytable GROUP BY personal_number HAVING COUNT(*) > 1 )
SELECT personal_number FROM mytable GROUP BY personal_number HAVING COUNT(*) > 1 )
Then, using php, retrieved personal_numbers join as string (soemthing like this
'222', '222' ) and run other query
SELECT name FROM mytable WHERE personal_number IN( here joined string )
Variant 2 works approximately 10 times faster, than variant 1, this is surprise for me, I was thinking that one query will be faster, but...
(In table is 500 000 rows, column
personal_number not indexed)
So, what you mean about cases like this? why variant 2 is many faster than variant 1 ?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `mytable` ADD INDEX `mytable_idx_personal_number` (`personal_number`);
SELECT mytable.name FROM mytable WHERE EXISTS ( SELECT 1 FROM mytable AS mytable1 WHERE ( mytable.personal_number = mytable1.personal_number ) GROUP BY mytable1.personal_number HAVING COUNT(*) > 1 ORDER BY NULL )