Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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 !!!

share|improve this answer
    
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 –  shihon Aug 16 '12 at 7:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.