neoform

It took me about two hours to track down the problem, but it seems a large INSERT has been killing my mysqli connection (in PHP). The query inserts 1 row with a serialized array in one of the fields. The query is 1.1MB, which seems to hose the connection. So, the question is, what is the size limit for a given mysqli query? Is this a mysql configration issue, or a mysqli limitation? My mysql wait timeout isn't being reached, since I set it to 900 seconds and the page is dying after about 20 seconds..

It took me about two hours to track down the problem, but it seems a large INSERT has been killing my mysqli connection (in PHP). The query inserts 1 row with a serialized array in one of the fields. The query is 1.1MB, which seems to hose the connection. So, the question is, what is the size limit for a given mysqli query? Is this a mysql configration issue, or a mysqli limitation?

Right now I'm completely perplexed as to why I'm getting a table scan instead of the primary index being used (i tried forcing the indexes, it still doesn't use it). It seems so simple it's making me tear out my hair. +----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+ | 1 | SIMPLE | _clicks | ALL | PRIMARY | NULL | NULL | NULL | 20 | | | 1 | SIMPLE | _transactions | eq_ref | PRIMARY,id | PRIMARY | 4 |_clicks.transaction_id | 1 | Using where | +----+-------------+---------------+--------+---------------+---------+---------+-------------------------------------+------+-------------+ 2 rows in set SELECT _transactions.amount, _clicks.blah FROM _transactions INNER JOIN _clicks ON _transactions.id = _clicks.transaction_id WHERE _transactions.posted_on BETWEEN '2008-09-14 00:00:00' AND '2008-09-24 09:48:00' CREATE TABLE `_clicks` ( `transaction_id` int(10) unsigned NOT NULL, `blah` char(5) NOT NULL, PRIMARY KEY (`transaction_id`) ); CREATE TABLE `_transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `amount` decimal(10,2) NOT NULL, `posted_on` datetime NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`posted_on`) ); INSERT INTO `_clicks` VALUES ('1', 'a'); INSERT INTO `_clicks` VALUES ('2', 'b'); INSERT INTO `_clicks` VALUES ('3', 'c'); INSERT INTO `_clicks` VALUES ('4', 'd'); INSERT INTO `_clicks` VALUES ('5', 'e'); INSERT INTO `_clicks` VALUES ('6', 'f'); INSERT INTO `_clicks` VALUES ('7', 'g'); INSERT INTO `_clicks` VALUES ('8', 'h'); INSERT INTO `_clicks` VALUES ('9', 'i'); INSERT INTO `_clicks` VALUES ('10', 'j'); INSERT INTO `_clicks` VALUES ('11', 'k'); INSERT INTO `_clicks` VALUES ('12', 'l'); INSERT INTO `_clicks` VALUES ('13', 'm'); INSERT INTO `_clicks` VALUES ('14', 'n'); INSERT INTO `_clicks` VALUES ('15', 'o'); INSERT INTO `_clicks` VALUES ('16', 'p'); INSERT INTO `_clicks` VALUES ('17', 'q'); INSERT INTO `_clicks` VALUES ('18', 'r'); INSERT INTO `_clicks` VALUES ('19', 's'); INSERT INTO `_clicks` VALUES ('20', 't'); INSERT INTO `_transactions` VALUES ('1', '24.00', '2008-09-01 10:37:42'); INSERT INTO `_transactions` VALUES ('2', '24.00', '2008-09-02 10:37:46'); INSERT INTO `_transactions` VALUES ('3', '23.00', '2008-09-03 10:37:50'); INSERT INTO `_transactions` VALUES ('4', '636.00', '2008-09-04 10:37:53'); INSERT INTO `_transactions` VALUES ('5', '545.00', '2008-09-05 10:37:55'); INSERT INTO `_transactions` VALUES ('6', '6.00', '2008-09-06 10:37:58'); INSERT INTO `_transactions` VALUES ('7', '46.00', '2008-09-07 10:38:01'); INSERT INTO `_transactions` VALUES ('8', '4.00', '2008-09-08 10:38:04'); INSERT INTO `_transactions` VALUES ('9', '45.01', '2008-09-09 10:38:08'); INSERT INTO `_transactions` VALUES ('10', '5.00', '2008-09-10 10:38:11'); INSERT INTO `_transactions` VALUES ('11', '8.00', '2008-09-11 10:38:14'); INSERT INTO `_transactions` VALUES ('12', '5.12', '2008-09-12 10:38:16'); INSERT INTO `_transactions` VALUES ('13', '7657.00', '2008-09-13 10:38:19'); INSERT INTO `_transactions` VALUES ('14', '79.00', '2008-09-14 10:38:22'); INSERT INTO `_transactions` VALUES ('15', '76.00', '2008-09-15 10:38:24'); INSERT INTO `_transactions` VALUES ('16', '6.00', '2008-09-16 10:38:27'); INSERT INTO `_transactions` VALUES ('17', '3453.00', '2008-09-17 10:38:30'); INSERT INTO `_transactions` VALUES ('18', '445.00', '2008-09-18 10:38:33'); INSERT INTO `_transactions` VALUES ('19', '657.00', '2008-09-19 10:38:35'); INSERT INTO `_transactions` VALUES ('20', '64.00', '2008-09-20 10:38:38');

I tried using fulltext for some tables that were a little over 1GB of text and it was really slow and bad results.. I then tried sphinx and suddenly the speed of both indexing and searching was massively faster, by at least 100x and far better results.. My question is, why does fulltext suck so bad? This seems like one of those things that mysql should be good at.. but it's not.

You can't really put "code" into images. You should however not allow the uploaded files to be directly accessible. Even if it means creating a php file that does "readfile()" and outputs the contents of that file, do that instead of allowing direct web level access to the file, this will stop people from uploading malicious files like .htaccess or hack.php (if a user uploads a php file, it would have access to all your code).

I was wondering what would be the best search application for indexing/searching content? Fulltext in MySQL is too slow once you get a db over 1GB. I played around with Zend's version of Lucene, but it contains a few bugs that need to be fixed before I can use it.. Does anyone know of other search libs/apps out there that would work well with php?

Hah, hadn't thought to do that one... I was starting to think I was going crazy. Seems it was just mysql thinking it was smarter than me and using no index instead of the one i provided. Works. Thanks

yep. It auto runs every 6 hours via cronjob. (and i just ran it right now to be sure).. same result. Could it be that it's being cause by the version I'm using? 5.1.26-rc-community

I actually did that. I got the same result. Which leads me to think that maybe I'm not seeing how the index is supposed to be designed..

I'm making a messaging system, but have run into a problem with one of my queries not using the proper index. I keep getting a "ALL" for the query. If I don't have the right index, what should it be? SELECT bodies.posted_on, bodies.user_id, recipients.thread_id id, SUM(IF(bodies.id > recipients.viewed_body_id, 1, 0)) new_messages FROM system_messages_recipients recipients INNER JOIN system_messages_bodies bodies ON recipients.thread_id = bodies.thread_id WHERE recipients.recipient_user_id = '1' AND bodies.id > recipients.delete_body_id +----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+ | 1 | SIMPLE | bodies | ALL | PRIMARY,posted_on | NULL | NULL | NULL | 400232 | | | 1 | SIMPLE | recipients | eq_ref | PRIMARY | PRIMARY | 7 | phpneoform.bodies.thread_id,const | 1 | Using where | +----+-------------+------------+--------+-------------------+---------+---------+-----------------------------------+--------+-------------+ -- Table "system_messages_bodies" DDL CREATE TABLE `system_messages_bodies` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `thread_id` int(10) unsigned NOT NULL, `posted_on` datetime NOT NULL, `user_id` mediumint( unsigned NOT NULL, `body` varchar(60000) NOT NULL, `body_hash` binary(20) NOT NULL, PRIMARY KEY (`id`), KEY `hash` (`body_hash`), KEY `posted_on` (`id`,`thread_id`,`posted_on`) ) ENGINE=MyISAM AUTO_INCREMENT=400233 DEFAULT CHARSET=latin1; -- Table "system_messages_recipients" DDL CREATE TABLE `system_messages_recipients` ( `thread_id` int(10) unsigned NOT NULL, `recipient_user_id` mediumint( unsigned NOT NULL, `viewed_body_id` int(10) unsigned NOT NULL, `delete_body_id` int(10) unsigned NOT NULL, PRIMARY KEY (`thread_id`,`recipient_user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I'm giving Zend's wrapper for Lucene a try.. Everything I've read so far online suggests that fulltext is only good for small tables, not big ones.

Just wondering, what have you used for searches then? I have a lot of text based content, what would be the right approach for search? On other projects I used a keyword/link approach, where I would store stemmed words and links with weight to each word to each item (product). The reason I'm trying something new is because my actual search query was never very good, I'd get relevant words, but often the most relevant items (products) were not at the top. Not to mention the keyword index was massive (often bigger than the content itself), and the indexing process was very CPU intensive..

The index is 110MB.. doesn't seem that big compared to some of the other indexes I have.

I set my key buffer to 2GB (the machine has 4GB of ram).. From what I can tell it's not paging, so I don't think that's the issue. 2Gb should be more than enough to fit the index.

well, that particular search term does, but in general, the query takes about 10-20 seconds. example: SELECT id FROM shop_products WHERE MATCH(name, description_short) AGAINST('dancing'); takes 16 seconds and returns 826 rows. mysql> explain SELECT id FROM shop_products WHERE MATCH(name, description_short) AGAINST('dancing'); +----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+ | 1 | SIMPLE | shop_products | fulltext | full | full | 0 | | 1 | Using where | +----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+ Keep in mind the DB is 6GB and has 300,000 rows.. Does this sound right?