| shimi | the old box had 4.0.16-max, the new one is 5.0.26-log. on the way, I converted my local character set from windows-1255 into utf-8, though I don't think that one is related |
| vak | jazzrocker, actually i'd need a DB driven by structure described in XML _queries_, not drven by how the data is _stored_. |
| DedParet | Got a q: I'm using Perl DBI to talk to mysql, and getting an error. Is it impossible to use "?" in a subquery, and pass the value in thru execute(val)? |
| shimi | anyways, here's what EXPLAIN says: +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+ | 1 | SIMPLE | news | range | dump | dump | 47 | NULL | 192826 | Using where; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+ |
| jazzrocker | vak: that doesn't make sense... the best answer i have to that comment is: then use XML, not a DB |
| fatpelt | shimi: ugh... don't flood :) |
| jazzrocker | ewwww... shimi fucking pastebin |
| DedParet | ...specifically, $sth=$dbh->prepare("select * from mytable where id not in (select id from foo where fieldx = ?)"); |
| fatpelt | shimi: so yeah, you need to optimize that query |
| DedParet | $sth->execute('bar') |
| jazzrocker | vak: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model http://www.dbazine.com/ofinterest/oi-articles/celko22 |
| shimi | the where is the problem, or the order by? or both... |
| vak | jazzrocker, OK, what backend could i use to sort all my XML stuff? :) |
| fatpelt | shimi: the doc page on the explain talks about what it means for "using where; using filesort" |
| jazzrocker | vak: C? Perl? PHP? Ruby? Python? anything that has an XML lib vak: even something that doesn't (you could write your own) |
| vak | jazzrocker, i'd like some (half-) ready stuff, don't want to write things from scratch |
| Leithal_ | DedParet: $sth->bind_param(1, 'bar'); $sth->execute(): |
| jazzrocker | vak: as i said: anything that's got an xml lib |
| Leithal_ | DedParet: and damn... use a left join... |
| jazzrocker | left join join ... !man left join |
| the_wench | see http://dev.mysql.com/doc/refman/5.0/en/join.html |
| shimi | ok here's the thing. the query selects all rows that have one of two "genre" values (with OR) and sorts according to unixtime column, descending. now I have the following index: genre,unixtime. That used to do the trick... I think. where am I wrong? |
| vak | jazzrocker, thanks |
| DedParet | leithal: ...I can use a left join, when I want to find those ids which *don't* show up in the second table? |
| jazzrocker | vak: sure... i still don't even know what you're doing, so who knows if my advice is right vak: my guess is that you're better off storing your data in XML |
| vak | jazzrocker, ok, but then i need search through the certain fields of this XML, like it were a DB. is there any advanced engine for that already? |
| fatpelt | shimi: is the genre,unixtime index called 'dump' ? |
| jazzrocker | vak: not all data benefits from storage in a relational DB... relational data does... hierarchical data does not... excessively variable data doesn't either (e.g. data sets that routinely have wildly varying property values for the same data type... e.g. as pointed out in the EAV article: medical records, or manufacturing processes) |
| Leithal_ | DedParet: that is the beauty of left joins |
| shimi | yes |