how to increase the performance of the mysql query

[chandbasha]

Hi
I have one sql query. using this query i can download 10 lakhs of data in csv file for 6 hours. now i want to download 20 lakhs of data for 6 hours or less. please tell me how to increase the performance.

SELECT id as imp_addr_id,userEmail, email ,name,mobile_no,gender,city,income,profession,experience,country,pincode,type FROM import_address_book_test WHERE blacklist!='1' AND email!='' AND ( email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' OR email like'[e-mail]' ) AND status=1 AND ( email NOT LIKE 'info@%' OR email NOT LIKE 'admin@%' OR email NOT LIKE'noreply@%' OR email NOT LIKE'sales@%' OR email NOT LIKE'no-reply@%' OR email NOT LIKE'mailer@%' OR email NOT LIKE'mailer-demon@%' OR email NOT LIKE'groups%@%' OR email NOT LIKE'noresponse@%' ) AND email NOT LIKE'%@%@%' AND cash_paid = '2' AND contest = '2' ORDER BY lastModified ASC

[pritaeas]

Can you also show your table structure, including keys/indexes ?

[chandbasha]

CREATE TABLE IF NOT EXISTS `import_address_book` (
`id` bigint(20) NOT NULL auto_increment,
`mobile_no` varchar(200) default NULL,
`user_id` bigint(20) NOT NULL,
`userEmail` varchar(255) NOT NULL,
`name` varchar(255) default NULL,
`email` varchar(2555) NOT NULL,
`type` varchar(100) NOT NULL default '1',
`country` varchar(200) NOT NULL default '1',
`gpwd` varchar(255) NOT NULL,
`status` int(2) NOT NULL default '1',
`dateCreated` timestamp NOT NULL default CURRENT_TIMESTAMP,
`lastModified` timestamp NOT NULL default '0000-00-00 00:00:00',
`gender` varchar(10) NOT NULL,
`city` int(100) NOT NULL,
`income` varchar(200) NOT NULL,
`profession` varchar(255) NOT NULL,
`experience` varchar(255) NOT NULL,
`pincode` int(10) NOT NULL,
`blacklist` varchar(11) NOT NULL default '0',
`paid_status` int(2) NOT NULL,
`contest_status` int(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `userEmail` (`userEmail`),
KEY `lastModified` (`lastModified`),
KEY `type` (`type`),
KEY `blacklist` (`blacklist`),
KEY `email` (`email`(1000))
) ENGINE=MyISAM ;

[smantscheff]

Export all rows in a text file. Fold your various in- an exclusion conditions into a regular expression and filter the textfile using grep. Might be much faster than mysql.

[jude.fernando]

do you really need that big a email field?

[jude.fernando]

Try eliminating these 2 conditions from your query - looks like they are redundant

email!='' AND

AND ( email NOT LIKE 'info@%' OR email NOT LIKE 'admin@%' OR email NOT LIKE'noreply@%' OR email NOT LIKE'sales@%' OR email NOT LIKE'no-reply@%' OR email NOT LIKE'mailer@%' OR email NOT LIKE'mailer-demon@%' OR email NOT LIKE'groups%@%' OR email NOT LIKE'noresponse@%' ) AND email NOT LIKE'%@%@%'

Note: When you use NOT LIKE the AND operator would be better instead of OR.

[voidyman]

if you have a large number of blakclisted entrie (blacklist=1), adding an index on that column will help your query time. And as smantscheff suggested do a grep -v -f file_containg_patterns filecontaining_output(or sed '/pattern/d') to exclude the unwanted email patterns.

[fobos]

Also you can create a cache.
open my.ini and change the following:
set query_cache_type to 1,2,or3
query-cache-type = ?
settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).
set query_cache_size to your expected size in MB
query-cache-size = 20M

To check if your mysql server already enable query cache, simply run this query:
SHOW VARIABLES LIKE ‘%query_cache%’;

To check if your MySQL query cache is working, simply perform a sql query for 2 times and check the query cache variable like below:
SHOW STATUS LIKE ‘%qcache%’;

For the first time you execute your SQL query, the time it should take take be longer compare to the second time query. This is due to the MySQL query cache is working!