Mysql – how to fetch different data on compare rows of two tables

indexjoin;MySQLselect

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.

Best Answer

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

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – get column from too many tables in thesql

Related Question