Video Searching

symtab · 2011-07-31 22:07:42

Hi,

I saw that many of you have problems with the video search and related videos (i personally use sphinx and it works perfectly even with 400.000 videos). The problem is that
the script only searches by tags (the exact name, to be more exact) so its very fast, however the results are limited. We already have sphinx, but for those that cant use sphinx (think shared hosting) i will implement a full text title and equal tag search. Basically the main search is done on the title, however if the same video also has a tag that is the search term, then it will be more relevant.

Let me know what you think about this.

symtab · 2011-07-31 22:23:16

This is the query:

SELECT v.video_id, v.title, t.name, MATCH (v.title) AGAINST ('home') AS relevance FROM video AS v LEFT JOIN video_tags AS t ON (t.video_id = v.video_id AND t.name = 'home') WHERE MATCH (v.title) AGAINST ('home' IN BOOLEAN MODE) AND v.status = 1 GROUP BY v.video_id ORDER BY IF(t.name IS NOT NULL, 10, relevance) DESC LIMIT 10;

MS_DA · 2011-08-01 02:52:16

I ran the query on on phpMyAdmin and got this error

#1191 - Can't find FULLTEXT index matching the column list

SELECT v.video_id, v.title, t.name,
MATCH (
v.title
)
AGAINST (
'home'
) AS relevance
FROM video AS v
LEFT JOIN video_tags AS t ON ( t.video_id = v.video_id
AND t.name = 'home' )
WHERE MATCH (
v.title
)
AGAINST (
'home'
IN BOOLEAN
MODE
)
AND v.status =1
GROUP BY v.video_id
ORDER BY IF( t.name IS NOT NULL , 10, relevance ) DESC
LIMIT 10 ;

symtab · 2011-08-01 08:03:13

You need to add a full text key first:

ALTER TABLE `video` ADD FULLTEXT KEY `title` (`title`);

Try now.

MS_DA · 2011-08-01 12:54:28

Well this gives me the error

#1061 - Duplicate key name 'title'
ALTER TABLE `video` ADD FULLTEXT KEY `title` ( `title` ) ;

symtab · 2011-08-01 13:18:42

Ahh...sorry:

ALTER TABLE `video` DROP KEY `title`;
ALTER TABLE `video` ADD FULLTEXT KEY `title` (`title`);

MS_DA · 2011-08-01 15:27:38

Now i get this

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `video` ADD FULLTEXT KEY `title` (`title`)' at line 2
ALTER TABLE `video` DROP KEY `title` ;
ALTER TABLE `video` ADD FULLTEXT KEY `title` ( `title` ) ;

Sorry!

Maybe this info helps:

Apache version 2.2.15
PHP version 5.2.13
MySQL version 5.0.92
Operating system freebsd
Kernel version 8.0-RELEASE-p3

symtab · 2011-08-01 21:52:06

Just email me mysql access :-)

symtab · 2011-08-03 12:25:08

Did you manage to make this working? Please email me mysql access and i will also test. This needs to work, it will be available by default in RC3.

MS_DA · 2011-08-04 11:09:53

Sorry Adrian i was mega busy with a major upgrade of our IPS main site to a completely new version with all the usual issues.

Didn´t figure the database thingi yet - sent you the needed datas now

Ben

symtab · 2011-08-04 21:10:29

Thanks. I just tested. It works...i droped the current key and added a fulltext key on `title`. Is added in RC3.

MS_DA · 2011-08-04 21:34:05

Cool

Many thanks Adrian

MS_DA · 2011-08-05 08:28:22

Well Adrian something seemed to changed in a bad way with this - guests get this error message now

Guest bandwidth limit reached. Please create a account or login to watch more videos!

if they try to watch their FIRST clip!

Ben

MS_DA · 2011-08-05 10:20:22

Well this is quite weird

I cannot watch any clip as guest - neither using firefox nor using ie . Mv911 can watch them as guest.

symtab · 2011-08-05 11:26:19

Ahh...go to Admin -> User -> Configure and set quest limit to no (i didnt change this, i only changed the database field, and thats it).

Adult Script Pro Community Forums

#1 2011-07-31 22:07:42

Video Searching

#2 2011-07-31 22:23:16

Re: Video Searching

#3 2011-08-01 02:52:16

Re: Video Searching

#4 2011-08-01 08:03:13

Re: Video Searching

#5 2011-08-01 12:54:28

Re: Video Searching

#6 2011-08-01 13:18:42

Re: Video Searching

#7 2011-08-01 15:27:38

Re: Video Searching

#8 2011-08-01 21:52:06

Re: Video Searching

#9 2011-08-03 12:25:08

Re: Video Searching

#10 2011-08-04 11:09:53

Re: Video Searching

#11 2011-08-04 21:10:29

Re: Video Searching

#12 2011-08-04 21:34:05

Re: Video Searching

#13 2011-08-05 08:28:22

Re: Video Searching

#14 2011-08-05 10:20:22

Re: Video Searching

#15 2011-08-05 11:26:19

Re: Video Searching

Board footer