Re: make mysql server use more cpu power

[Fred nolastname]

Hi, I have only 1 core.

There is a part of my stored procedure:

mysql> describe ad_content;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| Contid   | int(20)  | NO   | PRI | NULL    | auto_increment |
| Adid     | int(20)  | NO   | MUL | NULL    |                |
| Abstract | longtext | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


CREATE TEMPORARY TABLE shortened (
        sid INT(20),
        PRIMARY KEY (sid)
    );

        CREATE TEMPORARY TABLE final_table (
        adid INT(10),
        PRIMARY KEY (adid)
    );

IN content VARCHAR(200);

INSERT INTO final_table (final_table.adid)
         SELECT temp.adid FROM (SELECT * FROM ad_content JOIN shortened ON ad_content.adid = shortened.sid) AS temp
         WHERE temp.abstract LIKE '%content%';

I can't show the query that fills "shortened" table, but I am shore that it doesnt take more than 1 second.

All the time is taken by "INSERT INTO final_table" query because its joining two big tables. The "ad_content" and "shortened" tables have over 30.000 records. Also I have red that Mysql is not very good in JOINING big tables. Are there any easy workarounds? That is way I was thinking about configuring My.conf to squeeze everything from mysql and my server. By the way, my server has 512 MB of RAM and Intel(R) Xeon(R)CPU L5520 @ 2.27GHz

Thanks.



Edited 1 time(s). Last edit at 02/19/2011 06:05AM by Fred nolastname.