how to fetch different data on compare rows of two tables

Question

I have two tables in my database, one is user_app table in which user info are stored, table are as:

+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| apikey | varchar(32) | NO | | NULL | |
| app_id | varchar(16) | NO | | NULL | |
| appidt | varchar(100) | NO | | NULL | |
| imei_num | varchar(32) | NO | MUL | NULL | |
| app_version | varchar(20) | NO | | NULL | |
| package_name | varchar(100) | NO | MUL | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sdk_version | float | NO | | NULL | |
+--------------+--------------+------+-----+-------------------+-----------------------------+

Other table is Ad's table through which i retrieve ad's to user, table name creative are shown as:

+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| campaign_id | tinyint(4) | NO | | NULL | |
| status | tinyint(4) | NO | | NULL | |
| type | varchar(20) | NO | | NULL | |
| title | varchar(40) | NO | | NULL | |
| description | varchar(100) | NO | | NULL | |
| iconlink | text | NO | | NULL | |
| marketlink | text | NO | | NULL | |
| app_id | varchar(16) | YES | | NULL | |
| package_name | varchar(200) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+

Now i get all user info in user_app table via php scripts, along with package name which identify user application. On other hand, in creative table i am create some ad's which is send to user, package name also define here.

Here i am checking user application based on package_name, if package_name match in creative table with user_app, then i retrieve other ad and send to user. I am done with this query:

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM `user_app` AS ua INNER JOIN `creative` AS c ON ua.package_name <> c.package_name
where ua.imei_num = '30c899b5c0f29dbc95418c4173f6f745'
and c.package_name in (12,13) group by `package_name`

Problem is when a user has 5 or more app, he will be get different ad per app, but through this query i am able to retrieve only different one ad which is not similar to user app, but if user has 4 app ad's already then i have to send him different ad's.

Another statement is this query is run on server smoothly but sometime it logged as in mysql-slow query which takes 1-10 seconds, so i have to optimized this query for performance.

I apologize for my explanation if it is incomplete, but i am giving my best.

RolandoMySQLDBA: Can you please again have a look on this query and help me to solve this asap. Any help would be very greatful for me.

Answer 1

Here is your original query

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM `user_app` AS ua INNER JOIN `creative` AS c
ON ua.package_name <> c.package_name
where ua.imei_num = '30c899b5c0f29dbc95418c4173f6f745'
and c.package_name in (12,13)
group by `package_name`

I would like to suggest refactoring the query to retrieve needed rows before doing the JOIN

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM
(
 SELECT package_name FROM `user_app`
 WHERE imei_num = '30c899b5c0f29dbc95418c4173f6f745'
) AS ua INNER JOIN
(
 SELECT * FROM `creative`
 WHERE c.package_name in (12,13)
) AS c
ON ua.package_name <> c.package_name
group by ua.package_name;

Give it a Try !!!

• Shashank: I tried, but it gives me same output as previous query, in actual if i write there instead of c.package_name "where id IN (12,13) " means if a user package_name match 12 number id then he get 13 number id, and if a user have 4 packages and i am serving 8 ads, then he get another 4 packages from ad's